Excel to Access via ADO

[alvinmathew88]

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & vntPath _
& ";Extended Properties=""Excel 12.0 XML;HDR=Yes;"";"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


'Open Connection
cn.Open strConn


Dim strSQL As String


strPath = CurrentProject.FullName


Debug.Print strPath
strSQL = "Insert into [;Database=" & strPath & "].Table1 Select * From [" & strRangeAddress & "];"
Debug.Print strConn
Debug.Print strSQL
rs.Open strSQL, cn




rs.Close
cn.Close

- Code is contained in a module in the database located at "strPath" location.

- All variables are assigned values before the code snippet above.

- Im trying to insert the data from excel into Access. The error I get is "the database has been placed in a state by user 'Admin' on machine 'xxxxx' that prevents it from being opened or locked". its fails at this line : rs.Open strSQL, cn

- I would normally use DoCmd.Transferspreadsheet, but the field names don't correlate with excel header fields.

What do I need to modify to get my code working?

Please help. Thanks in advance.

[June7]

You don't open a recordset with an action SQL. Recordsets are based on SELECT queries. You don't even need a recordset object in this case just to populate table. I've never tried building a query from an Excel range.

Try:

Dim strSQL As String, strConn As String, strPath As String, strRangeAddress As String, vntPath As Variant, cn As ADODB.Connection

'where is code that sets strRangeAddress and vntPath?

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & vntPath _
& ";Extended Properties=""Excel 12.0 XML;HDR=Yes;"";"

Set cn = New ADODB.Connection
cn.Open strConn

strPath = CurrentProject.FullName
strSQL = "Insert into [;Database=" & strPath & "].Table1 Select * From [" & strRangeAddress & "];"
CurrentDb.Execute strSQL

cn.Close

[alvinmathew88]

The issue was having resolved by Compacting and Repairing the database.
I ended up actually using CurrentDb.execute before you suggested it. Thanks!