Insert data into ACCESS from EXCEL run VBA.
I want to insert data from an Excel range into an ACCESS database and run the procedure from Excel. After some research, I came across a varition of below but I keep getting an "Automation Error". But as far can I can tell, the code is correct. Any suggestions? TESTINGUPLOAD is the name of the range in the Excel workbook. It gets to the Execute statement and then the error appears.
Private Sub cmdOpenDatabase_Click()
Dim conn As ADODB.Connection
Dim strSQL As String
strSQL = "INSERT INTO tblTESTING SELECT * FROM OPENROWSET([EXCEL 8.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=Yes].[TESTINGUPLOAD]
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
conn.ConnectionString = strDatabaseTestingpath
conn.Open
conn.Execute (strSQL)
conn.Close
End Sub
Private Sub cmdOpenDatabase_Click()
Dim conn As ADODB.Connection
Dim strSQL As String
strSQL = "INSERT INTO tblTESTING SELECT * FROM OPENROWSET([EXCEL 8.0;DATABASE=" & ThisWorkbook.FullName & ";HDR=Yes].[TESTINGUPLOAD]
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0;"
conn.ConnectionString = strDatabaseTestingpath
conn.Open
conn.Execute (strSQL)
conn.Close
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks.
ASKER