Exporting tables to SQL server using VBA (1 Viewer)

Giobbe

New member
Local time
Tomorrow, 00:26
Joined
Dec 8, 2021
Messages
8
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:26
Joined
May 7, 2009
Messages
19,169
i am not sure if that is possible.
what will work is you export your table to excel file and
use MSSMS to import the excel file.

or use External data on ribbon and export it to ODBC database.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:26
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Are you trying to migrate the entire database or just a few tables?
 

Giobbe

New member
Local time
Tomorrow, 00:26
Joined
Dec 8, 2021
Messages
8
Hi. Welcome to AWF!

Are you trying to migrate the entire database or just a few tables?
Hi, just a few tables; and I need to automate the process (for example with a button in a form: when I press that button, some tables are exported
to the SQL server using the VBA code)

Marco
 

Giobbe

New member
Local time
Tomorrow, 00:26
Joined
Dec 8, 2021
Messages
8
i am not sure if that is possible.
what will work is you export your table to excel file and
use MSSMS to import the excel file.

or use External data on ribbon and export it to ODBC database.
Hi, I need to automate the process: what I'm trying to do is, I suppose, the VBA version of the last operation you suggested (TransferDatabase with acExport and "ODBC Database" parameters). But it works only for one table, after the first transfer I have to close and reopen access database
Marco
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:26
Joined
May 7, 2009
Messages
19,169
here i modified your function.
you can check and test:
Code:
' Modified by Arnelgp
'________________________
'
Public Function exportTable(table As String, destTable As String) As Boolean
    ' you need to Provide the User (UID) and Password (PWD)
    Const CON As String = _
            "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=192.168.0.1;" & _
            "UID=XXXX;" & _
            "PWD=XXXX;" & _
            "Trusted_Connection=No;APP=SSMA;DATABASE=DBname;"
    Dim sql As String
On Error GoTo ERR_HANDLER
    sql = "SELECT * INTO [" & CON & "].[" & destTable & "] FROM [" & table & "];"
    CurrentDb.Execute sql
    exportTable = True
EXIT_FUNC:
    Exit Function
ERR_HANDLER:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume EXIT_FUNC
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
 
Last edited:

Giobbe

New member
Local time
Tomorrow, 00:26
Joined
Dec 8, 2021
Messages
8
hi Marco @Giobbe

You need to give it time to finish processing before moving on ... or Access will just think it can go on its merry way

here is some code and explanation on using the Sleep API
Hi @strive4peace, thanks for the suggestion; unfortunately I've tried the Sleep sub found in the page, using a sleep of 30 seconds between calls, but nothing changes; there are big tables, but also short; I've tried with short table first and with a medium table first, but the result is the same error and only the first table is transferred successfully
Kind Regards
Marco
 

Giobbe

New member
Local time
Tomorrow, 00:26
Joined
Dec 8, 2021
Messages
8
here i modified your function.
you can check and test:
Code:
' Modified by Arnelgp
'________________________
'
Public Function exportTable(table As String, destTable As String) As Boolean
    ' you need to Provide the User (UID) and Password (PWD)
    Const CON As String = _
            "ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=192.168.0.1;" & _
            "UID=XXXX;" & _
            "PWD=XXXX;" & _
            "Trusted_Connection=No;APP=SSMA;DATABASE=DBname;"
    Dim sql As String
On Error GoTo ERR_HANDLER
    sql = "SELECT * INTO [" & CON & "].[" & destTable & "] FROM [" & table & "];"
    CurrentDb.Execute sql
    exportTable = True
EXIT_FUNC:
    Exit Function
ERR_HANDLER:
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume EXIT_FUNC
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
Hi @arnelgp, thank you very much for the code. I've tried it, also in combination with @strive4peace suggestion, but nothing change, first table is transferred to SQL server; subsequent calls fail (table is created on the SQL server, but data is not transferred)
But thank to your code, I've learned a way to use SELECT INTO with ODBC tables that I didn't know at all :)
Marco
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:26
Joined
May 7, 2009
Messages
19,169
i have experienced with ODBC Driver 17.
it is slow in connecting the server.

maybe changing it will work for you:

DRIVER=SQL Server Native Client 11.0
 

Giobbe

New member
Local time
Tomorrow, 00:26
Joined
Dec 8, 2021
Messages
8
Hello @arnelgp , I've tried another couple of drivers, but nothing changes, even with a pause (sleep) among transfers.
I think I'll create a fake database that opens the problematic database, transfer the first table, close and reopen the database and so on.
Very strange problem
Thank you
 

strive4peace

AWF VIP
Local time
Today, 18:26
Joined
Apr 3, 2020
Messages
1,003
hi Marco @Giobbe

did you compile and save the code? It's good to specifically do that in the problem environment.
 

Minty

AWF VIP
Local time
Today, 23:26
Joined
Jul 26, 2013
Messages
10,355
i have experienced with ODBC Driver 17.
it is slow in connecting the server.

maybe changing it will work for you:

DRIVER=SQL Server Native Client 11.0
@arnelgp - I have to disagree , we use v17 for all our SQL Backends and it outperforms the earlier versions, it also adds support for newer functions.
If you are finding it slow, I'd like to see a comparison and maybe what version of SQL Server are you using. (We predominantly use Azure)?
 

Minty

AWF VIP
Local time
Today, 23:26
Joined
Jul 26, 2013
Messages
10,355
I've learned a way to use SELECT INTO with ODBC tables that I didn't know at all

This is by far the most efficient way I would have thought.
Truncate the original target table if you need to retain indexes, primary keys etc, and do an insert.
Or Delete completely then SELECT INTO.

A delete can take some time if there are indexes and lots of data, TRUNCATE doesn't use transaction logging so is much faster on SQL server.
 

Giobbe

New member
Local time
Tomorrow, 00:26
Joined
Dec 8, 2021
Messages
8
hi Marco @Giobbe

did you compile and save the code? It's good to specifically do that in the problem environment.
Hi @strive4peace, I didn't compile, the db is under active development
However I've created a second database, with a little script that opens and closes the problematic db, transferring one table on every cycle; I'm not
proud of it, but it's working :). I'll face this problem again when I'll have some time; if I'll ever find a solution I'll post here
Many thanks to you and to everyone that helped me
Marco
 

strive4peace

AWF VIP
Local time
Today, 18:26
Joined
Apr 3, 2020
Messages
1,003
hi Marco @Giobbe

you're welcome

>"I didn't compile, the db is under active development"

The database should ALWAYS be compiled before testing

There's an option to 'Compile on Demand' on the General tab of Options in the VBA editor -- I always turn it off though since I'm diligent about compiling and don't want Access to do it for me

If statements have issues -- fix them or comment them -- if you comment them out, also add another comment so you can easily find them when you search -- I often use 'fix blah blah so I can look for 'fix
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 18:26
Joined
Apr 3, 2020
Messages
1,003
hi Marco @Giobbe

you're welcome! I edited my previous comment to add more ... so be sure to read that too ~
 

GPGeorge

Grover Park George
Local time
Today, 16:26
Joined
Nov 25, 2004
Messages
1,776
Here's the actual error returned on the second attempt.

1639238674880.png


Apparently Access' TransferDatabase export to SQL Server turns on Identity_Insert for that table, but does not turn it off again. SQL Server permits only one table at a time to have Identity_Insert set ON. You'll have to use a passthru to run a stored proc on the database to turn Identity_Insert off again after each table has been exported.
 

bastanu

AWF VIP
Local time
Today, 16:26
Joined
Apr 13, 2010
Messages
1,401
I am attaching two versions of an Access utility I created years ago to load Access data into SQL tables - it has the code needed to turn on and off the Identity Insert.

Cheers,
 

Attachments

  • AccessToSQLMigration-DataMover.zip
    179.4 KB · Views: 416

Users who are viewing this thread

Top Bottom