Database Search

TechHome

• CLR Scripts to Create Linked Servers
• Create an XML Schema File from a SQL Server Database
• Get Database Results into a WIL Array and Display
• Insert Access Data to Excel
• Insert Excel Data Into Access
• OleDb GetSchema
• OleDb

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Insert Excel Data Into Access

 Keywords: Insert Select Excel Data Into Access ACE OLEDB Provider Excel 12.0 Xml 

;Winbatch 2013 - CLR - OLEDB: Insert Excel Data Into Access
;
;USES: Test.accdb and test.xlsx 
;
;REQUIRES: ACE OLEDB Provider (installed with Office 2007/2010)
;          .NET Assemblies 4.0
;
;TESTED: Win7 32-bit
;
;Stan Littlefield July 17, 2013
;////////////////////////////////////////////////////////////////////////////////////////////////////////
;do required files exist
cACCDB = Dirscript():"test.accdb"
If ! FileExist(cACCDB) Then Terminate(@TRUE,"Cannot Continue","Missing Database File: ":cACCDB)
cXLS = Dirscript():"test.xlsx"
If ! FileExist(cXLS) Then Terminate(@TRUE,"Cannot Continue","Missing Excel File: ":cXLS)
;create Display Window
BoxOpen("Please Wait","Inserting Excel Sheet Into Access...")
;create connection string and initiate Provider
cConn = 'Provider=Microsoft.ACE.OLEDB.12.0;data source=':cACCDB  ; cannot use ';Connect Timeout=30;' 
ObjectClrOption("use","System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
oProvider = ObjectClrNew("System.Data.OleDb.OleDbConnection",cConn)
oProvider.Open()

;issue "SELECT INTO" with Command Object to create Excel Tab via Office ISAM Drivers
;NOTE: the SQL will create a new tab named DOW, but if that Tab already exists either an error will occur
;      or a DOW1 tab will be created.
oCommand = ObjectClrNew("System.Data.OleDb.OleDbCommand")
oCommand.Connection = oProvider
oCommand.CommandText = 'INSERT INTO [DOW] SELECT * FROM [DOW$] IN "" [Excel 12.0 Xml;DATABASE=%cXLS%;HDR=YES;IMEX=1];'
oCommand.CommandTimeout = 30
oCommand.ExecuteNonQuery() ;no need to return a recordset object
;recover memory before exiting script
oCommand.Dispose()
oCommand=0 
oProvider.Dispose()
oProvider=0
BoxShut() ;close Display Window
Exit
;/////////////////////////////////////////////////////////////////////////////////////////////////////////////  
 

Article ID:   W17813

Filename:   Insert Excel Data Into Access.txt

File Created: 2013:07:18:09:10:24

Last Updated: 2013:07:18:09:10:24