[VBA] Inserting Data from Excel to Access Database

cmschlatt

New Member
Joined
Jun 3, 2015
Messages
10
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 :confused:. 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:

Code:
cn.Execute ssql

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

Here is the code:

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!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

cmschlatt

New Member
Joined
Jun 3, 2015
Messages
10
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,093
Messages
5,622,666
Members
415,917
Latest member
kungsleden

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top