Error connecting to access database from VS 2017

[J-Bal]

I am having an issue connecting to an access database to upload some data from SQL in the access database. This process was working before but we have migrated to a new server and the process is no longer working.

Here is my code:


Dim g_objCn As New ADODB.Connection
Dim g_strConnectionString As String = "C:\database\data.accdb"
Dim connsql = New SqlConnection

connsql.ConnectionString = "integrated security=SSPI;data source=sqlserver;" &
"persist security info=False;initial catalog=MainTables;MultipleActiveResultSets=true;"
Try
connsql.Open()
g_objCn.Provider = "Microsoft.ACE.OLEDB.16.0"
g_objCn.Properties("Data Source").Value = g_strConnectionString
g_objCn.Open()
g_objCn.Execute("INSERT INTO [C:\database\data.accdb].tblData (id, FacN, LName,FName)" &
"SELECT id, FacN, LName,FName " &
"FROM [tblData] IN '' [ODBC;Driver={SQL Server};Server=sqlserver;Database=MainTables;Trusted_Connection=yes]")


I am getting the error below on the line g_objCn.Provider = "Microsoft.Ace.oledb.16.0". I have checked the new server and that library is there (aceoledb.dll). So I am not sure what I am missing.



Continue reading...