Hi, I'm trying to transfer some tables from Access 2016 to SQL server; I'm using the following code:
------------------------------------
Public Function exportTable(table As String, destTable As String)
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=192.168.0.1;UID=XXXX;PWD=XXXX;Trusted_Connection=No;APP=SSMA;DATABASE=DBname;", _
acTable, table, destTable
End Function
Public Sub exportToSQLserver()
Dim a as Variant
Dim b as Variant
Dim c as Variant
a = exportTable("Source tablename1", "Dest tablename1")
b = exportTable("Source tablename2", "Dest tablename2")
c = exportTable("Source tablename3", "Dest tablename3")
End Sub
------------------------------------
exportToSQLserver works only for the first exportTable call (i.e. "Source tablename1"); the second call ("Source tablename2") creates the table on SQL server
("Dest tablename2") and then fails with error '3146', without exporting any data. But if I close Access, reopen it and execute only the second call ("Source tablename2") it works perfectly. I've found that the code works only for 1 call, the second fails everytime, no matter what the table is.
The workaround is to close and reopen Access database between calls; I suspect I should close the connection opened by TransferDatabase, but I don't know how
Any hints ? Suggestion ?
Thank you
Marco
P.S.: I forgot to remove "APP=SSMA" from the call, but I don't think it is the problem
------------------------------------
Public Function exportTable(table As String, destTable As String)
DoCmd.TransferDatabase acExport, "ODBC Database", _
"ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=192.168.0.1;UID=XXXX;PWD=XXXX;Trusted_Connection=No;APP=SSMA;DATABASE=DBname;", _
acTable, table, destTable
End Function
Public Sub exportToSQLserver()
Dim a as Variant
Dim b as Variant
Dim c as Variant
a = exportTable("Source tablename1", "Dest tablename1")
b = exportTable("Source tablename2", "Dest tablename2")
c = exportTable("Source tablename3", "Dest tablename3")
End Sub
------------------------------------
exportToSQLserver works only for the first exportTable call (i.e. "Source tablename1"); the second call ("Source tablename2") creates the table on SQL server
("Dest tablename2") and then fails with error '3146', without exporting any data. But if I close Access, reopen it and execute only the second call ("Source tablename2") it works perfectly. I've found that the code works only for 1 call, the second fails everytime, no matter what the table is.
The workaround is to close and reopen Access database between calls; I suspect I should close the connection opened by TransferDatabase, but I don't know how
Any hints ? Suggestion ?
Thank you
Marco
P.S.: I forgot to remove "APP=SSMA" from the call, but I don't think it is the problem