1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MS Access query (export excel to access)

Discussion in 'The Lounge' started by ThrottleWorks, Oct 4, 2017.

  1. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,879
    Hi,

    I am aware we are a Excel focussed forum, hence apologies for posting MS Access question here.

    I am new to MS Access, know 0 about this platform.
    Have copied code from https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table

    I have couple of doubts, if anyone can please help me in this.
    The current code opens existing database to paste the data.
    Every time I run the macro, data gets added in existing data.

    How do I define active database

    How do I clear contents from 'tblExcelImport' of 'Database4'

    Blocking acc.CloseCurrentDatabase or acc.Quit does not made any difference to code working. My assumption was, by blocking these lines, database will remain open

    How to create a new database for pasting range mentioned below.
    Present code required existing database.

    I am new to Access, hence have used terms used in excel only.
    Sorry if terms used are misleading.

    Also, first header name in excel is Sr. however in MS Access it is reflecting as Sr#
    Is it a normal behaviour ?

    Please note, this question is partially posted at https://www.mrexcel.com/forum/microsoft-access/1025350-how-export-data-excel-ms-access.html

    Code (vb):

    Sub ExportExceltoAccess()
        acc.OpenCurrentDatabase "\\YoYo\YoYo\YoYo\My Documents\Database4.accdb"
        acc.DoCmd.TransferSpreadsheet TransferType:=acImport, _
        SpreadSheetType:=acSpreadsheetTypeExcel12Xml, TableName:="tblExcelImport", _
        Filename:=Application.ActiveWorkbook.FullName, HasFieldNames:=True, _
        Range:="A1:C5"
        acc.CloseCurrentDatabase
        acc.Quit
        Set acc = Nothing
     
        MsgBox "Done !"
    End Sub
    Last edited: Oct 4, 2017
  2. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    An alternative to exporting data from Excel to Access is to link your Excel Range/Table to Access. In this manner, every time you update your Excel spreadsheet, it will automatically update the linked table in Access. You do not require VBA to do this.

    Open Access, External Data, New Data Source, From File, excel, Select the file to link and select the radio button for linking and save.

    No worries about duplications when done in this manner.
    ThrottleWorks likes this.
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,052
    Typically I'd recommend creating procedure to import data on Access side.

    The code looks incomplete. What is "acc"? I assume it's new Access.Application.

    Data base is .accdb file you are accessing. i.e.
    Code (vb):
    "\\YoYo\YoYo\YoYo\My Documents\Database4.accdb"
    The table to which you import the data is defined in TableName argument.
    Code (vb):
    TableName:="tblExcelImport"
    To delete all records in table, from Excel VBA... I'd use adodb. Though again, I usually have query on Access/DB side that I execute to delete records.

    If using Access.Application, you can use .DoCmd.RunSQL
    Code (vb):
    acc.DoCmd.RunSQL "DELETE * FROM TableName"
    Not sure about creating new DB from Excel VBA.
    Have a read of the link.
    http://software-solutions-online.com/vba-create-new-access-database-from-excel/

    Not sure the exact cause of "." converting to "#". But it is never a good idea to use special character in column name/header. Always use, letters, numbers, and underscore only.
    ThrottleWorks likes this.
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    407
    Creating a DB from Excel. I am thinking you could use SQL embedded in VBA. Look at this SQL code for creating a DB

    https://www.w3schools.com/sql/sql_create_db.asp

    and then use SQL code to develop a table in a similar manner

    https://www.w3schools.com/sql/sql_create_table.asp

    It might be easier to just develop all in Access, as you would need to develop your forms in Access. I don't think that would be possible outside of Access. Queries could be developed in VBA/SQL in Excel however.

    It is usually not a good idea to delete records in a DB. It is better to have a field that is used to indicate that the record is archived. In this manner, you can remove the information from your query results, but still have the historical information should you ever need it.
    Last edited: Oct 4, 2017
    ThrottleWorks likes this.
  5. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,879
    Hi @AlanSidman and @Chihiro , thanks a lot for the help.
    Please allow me some to study your posts. Will revert with details.

    Have a nice day ahead. :)
  6. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,879
    Hi @Chihiro sir, yes. My mistake.

    Dim MyAccess As New Access.Application
    MyAccess.OpenCurrentDatabase "\\A\B\C\My Documents\Database4.accdb"
  7. edwards142

    edwards142 New Member

    Messages:
    2
    ThrottleWorks likes this.

Share This Page