Thank you for reply everyone!
Please review my VBA codes as below which I have written in Excel VBA Window. Now I am looking for a solution with codes which runs the program using ADODB connection. Actually, I am unable to identify that how to use ".TransferSpreadsheet acImport" trick using ADODB codes.
Code:
Sub Data_ImportFromExcel_ToAccess()
' Below code is in Excel VBA Window, everything is running from there.
' User must add "Microsoft Access xx.x Object Library" reference to Excel VBA Codes
' Note - Below code is working, but I am looking for ADODB method (codes) to perform below steps.
Dim oAppAccess As Access.Application
Dim dbFullFilepath As String ' Full file path for Master Database in MS Access
Dim xlFullFilePath As String ' To import data from this Excel file to Database
dbFullFilepath = ThisWorkbook.Path & "\" & "Master_DB.mdb"
xlFullFilePath = ThisWorkbook.Path & "\" & "1. ScoreCard - Jan 2015.xlsx"
'Create Access Object
Set oAppAccess = CreateObject("Access.Application")
oAppAccess.OpenCurrentDatabase dbFullFilepath 'Note - below command actually opens the database
'Now Import data from Excel sheet to Database (in "tbl_tempImport" table)
oAppAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "tbl_tempImport", xlFullFilePath, True, "Sheet1!A:Z", True
'Refresh, Save and Close the database
oAppAccess.CurrentDb.TableDefs.Refresh
oAppAccess.DBEngine.Workspaces.Refresh
oAppAccess.Quit
Set oAppAccess = Nothing
End Sub
I hope now above sample gives some more clarity that what I am actually looking for...
Note - This line "oAppAccess.OpenCurrentDatabase dbFullFilepath" in above code opens the Access Database and make it visible, which it should not be. So also looking for ADODB method.
I highly appreciate your great efforts!
Regards