[VBA] Inserting Data from Excel to Access Database

[cmschlatt]

Hi all, I'm having issues with some code that takes lines of data entered by the user and inserts them into an Access database. I swear this code was running correctly a couple days ago and has started misbehaving today . I've double checked that the excel headers match the database, but cannot figure out what is going on! When I debug the code, it errors out at:

cn.Execute ssql

And returns this error:
Run-time error 2147467259 (80004005)
Automation error
Unspecified error

Here is the code:

Public Sub DoTrans()

Set cn = CreateObject("ADODB.Connection")
dbPath = "H:\Market Pricing Strategy\REPO\Repo_Main.accdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
cn.Open scn
ssql = "INSERT INTO REPO_IT ([Action Date], [Review Date], [Market], [Market Grouping], [Action Theme], [Test Active], [APs Impacted], [Notes]) "
ssql = ssql & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh


[B]cn.Execute ssql << ERROR HERE


[/B]If Err = 0 Then MsgBox "Complete!!!", vbInformation


End Sub

Thank you for any help!

 

[cmschlatt]

I possibly figured it out? On the excel sheet where the user enters the data they want to upload, I have a button that starts the macro in A1 and the user enters data starting in B2. When I deleted the button, deleted Column A, and created another button in a column to the right of my data, the macro worked fine!!! Not sure why this worked, but I'll take it.