I'm very new to VBA, and I've been trying to write some code in Excel 2016 that will, when a button is pressed, take a range of cells and write them to a table that I've set up in an Access 2016 database. I'm working out of an .xlsm workbook right now.
I've been basing my code off of the bulk-insert method (building an SQL "INSERT INTO" statement) that the submitter posted as an edit to this previous StackOverflow question Using Excel VBA to export data to MS Access table .
So far, here is the VBA I have written (or at least the part of it dealing with the importing into Access). When I run it, I get the error "Run-time error '-2147467259 (80004005)': Could not find installable ISAM.". I think the issue is in my connection string, which I've been having trouble with since I can't find any examples of properly-formatted connection strings on the Internet that use Access 2016 and Excel 2016 (mostly older versions).
' Set up the transfer into Access
Dim dbPath As String, dbWb As String, dbWs As String, scn As String, dsh As String, ssql As String
Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\MyDatabase.accdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]" & "Data2" 'Data2 is the named range of data to be written to Access
' Open the connection with Access
cn.Open scn
' Create an SQL "Insert" statement using the name dsh developed earlier
ssql = "INSERT INTO DataPortfolio ([Field1], [Field2], [Field3]) "
ssql = ssql & "SELECT * FROM [Excel 16.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
' Execute the SQL statement
cn.Execute ssql
Can anyone see where I may be going wrong? I assume it's in the connection string (scn), but I'm not sure.
Any help is appreciated! Many thanks.
