• 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...

  • 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)

ThrottleWorks

Excel Ninja
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:
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:
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.
 
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:
"\\YoYo\YoYo\YoYo\My Documents\Database4.accdb"

The table to which you import the data is defined in TableName argument.
Code:
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:
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.
 
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.

How do I clear contents from 'tblExcelImport' of 'Database4'
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:
Back
Top