Export from Ms Access to SQL Server

[abenitez77]

is there a way to export from ms access to SQL Server using vba code?

 

[isladogs]

Use the upsizing wizard to export tables to SQL Server.
If you use attachment fields or multi value fields these will need to be converted to a datatype that is allowed in sql server

 

[Pat Hartman]

What are you trying to do? Is this a one time conversion or do you need to transfer data on a regular basis? Are the tables already built in SQL Server or do you need to also create them?

SSMA is the SQL Server Migration Assistant. The newest versions are problematic because for some reason MS has decided to no longer support 32-bit databases and that is the vast majority of all Access apps.

I would suggest using a third party app such as SQL Server Examiner Suite so you can avoid the "free" SSMA tool that will wreck you PC if you follow the MS directions for installing it. I still cannot open .mde or .accde or .accdr databases AT ALL since I followed the MS directions.

 

[isladogs]

What are you trying to do? Is this a one time conversion or do you need to transfer data on a regular basis? Are the tables already built in SQL Server or do you need to also create them?

SSMA is the SQL Server Migration Assistant. The newest versions are problematic because for some reason MS has decided to no longer support 32-bit databases and that is the vast majority of all Access apps.

I would suggest using a third party app such as SQL Server Examiner Suite so you can avoid the "free" SSMA tool that will wreck you PC if you follow the MS directions for installing it. I still cannot open .mde or .accde or .accdr databases AT ALL since I followed the MS directions.

Sorry Pat but I don't understand what you're saying
You can use the upsizing wizard that is built into Access ...
- Database Tools ... Move Data .. SQL Server

This definitely works for 32-bit Access databases (at least when I last did this a few months ago)
I've never needed to do so for 64-bit but I'm sure that will work also.

I don't see any reason to use a third party tool unless it offers significant additional functionality

 

[The_Doc_Man]

My question is what the OP meant by "export" since that COULD be simply coping data already in an Access database to an SQL database that isn't the target of an up-size; it is just sharing / reconciling data with a second database.

abenitez77 - can you tell us a little more about what you wanted to do and how often you expect you would have to do it? That would answer our questions about the best way to do what you want - and trust me, what you want should be eminently possible. But your question was just a little ambiguous. Refine the question and you'll get a fine answer.

 

[Galaxiom]

If you are moving big data from Access up to existing tables on SQL Server, note that it is incredibly slow through linked tables. I don't know if this applies to the upsizer in Access.

SQL Server has a very powerful Import Wizard that is much faster but it does take some time to get one's head around it.

 

[isladogs]

If you are moving big data from Access up to existing tables on SQL Server, note that it is incredibly slow through linked tables. I don't know if this applies to the upsizer in Access.

SQL Server has a very powerful Import Wizard that is much faster but it does take some time to get one's head around it.

Why do you stress linked tables here?
The upsizing is done on the backend.

It will indeed take some time for a large datafile but of course it should only need to be done once.

From my limited experience using it, the SQL import / export wizard is also very good.
Is it faster? I've not tried doing the same database from each direction to test that
Is it less intuitive? I thought both methods were similar in terms of usability

My question was prompted by wanting more information regarding Pat's comment about the conversion no longer working with 32-bit databases.

 

[Pat Hartman]

You can use the upsizing wizard that is built into Access ...
- Database Tools ... Move Data .. SQL Server

Not any more you can't. MS deprecated it either in A2016 or A2013. The only thing left is the splitter that creates a separate Jet or ACE BE.

The upsizing wizard in Access was an excellent tool. SSMA was OK in earlier versions. The current version cut us off at the knees. In private I would have used the "f" word MS messed this up so badly. They know it and near as I can tell, it isn't fixed nor do they intend to fix it. So, unless you want to do it yourself or have a 64bit install of Office where you can run SSMA (I'm assuming that will work but I don't know), you are dead. Or worse, if you follow their directions to install the A2013 runtime. So, I am promoting a third party product.

 

[isladogs]

True - dropped for A2013
As I mainly use 2010, I had forgotten that.
Yet another reason to stick with 2010....!

Nevertheless the SQL Server Import/Export wizard (SSIEW) is still available (at least in SSMS 2014) & works well.
It is more powerful than the Access export equivalent
Is that what you are referring to as the SS Migration Assistant?

I've just run a quick test on a 32-bit Access database using this SSIEW.
As I remembered correctly, it still works.

So are you saying this feature has been dropped from SQL Server 2016 or that it has been restricted to 64-bit database only?

 

[abenitez77]

The user in the ms access app, selects the excel table and then it gets linked to ms access. I want to then export that to SQL Server.

I tried exporting to SQL using this :

 AmzConn = "ODBC;Description=SQL PassThru;DRIVER={" & GBL_strDriverVersion & "};SERVER=" & AmzSrv & ";DATABASE=" & AmzDb & ";TRUSTED_CONNECTION=Yes;"

' Tried it this way..   
DoCmd.TransferDatabase acExport, AmzConn, acTable, "ToolSettings", "ToolSettings"


'Then this way.
DoCmd.TransferDatabase acExport, "ODBC Database,ODBC;DSN=AmzExportToSQL;UID=;PWD=;LANGUAGE=us_english; DATABASE=Amazon_Global_Tool", acTable, "ToolSettings", "ToolSettings"

' I get the same Run-time error '13': type mismatch

 

[isladogs]

First thing is to check the datatypes in the table to see if any are not supported in SQL Server

 

[abenitez77]

First thing is to check the datatypes in the table to see if any are not supported in SQL Server

Only 5 fields and they are all Text fields. Shouldn't be a problem.

 

[isladogs]

Have a look at this code with a variety of connection strings & see if it helps

'##########################################
' GetConnectionString 04/08/2010 
'   Used to create a connection string
'############################################
Function GetConnectionString(strLinkType, _
                            strLinkServer, _
                            strLinkDatabase, _
			    strAppName, _	
                            strLinkUsername, _
                            strLinkPassword) As String
        
    Select Case strLinkType 'Get the link type to determine the connection string
#If SQL_DRIVER = "Native" Then
        Case "SQL"
            GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=strAppName;" & _
                                    "UID=" & Nz(strLinkUsername, "") & ";" & _
                                    "PWD=" & Nz(strLinkPassword, "")
        Case "SQL-Trusted"
            GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=strAppName;" & _
                                    "Trusted_Connection=yes;"
#ElseIf SQL_DRIVER = "MDAC" Then
        Case "SQL"
            GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=strAppName;" & _
                                    "UID=" & Nz(strLinkUsername, "") & ";" & _
                                    "PWD=" & Nz(strLinkPassword, "")
        Case "SQL-Trusted"
            GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                    "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                    "APP=strAppName;" & _
                                    "Trusted_Connection=yes;" & _
                                    ""
#End If
        Case "Access"
            If Nz(strLinkPassword, "") <> "" Then
                GetConnectionString = "MS Access;" & _
                                    "PWD=" & Nz(strLinkPassword, "") & ";" & _
                                    "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
            Else
                GetConnectionString = "MS Access;" & _
                                    "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
            End If
        Case Else
            GetConnectionString = ""
    End Select


End Function

There is also a website devoted to this specific topic - do a Google search

OR use the Import wizard in SQL Server and try importing the table instead

 

[Pat Hartman]

Are you trying to create a new table in SQL Server or append data to an existing table?

If you are trying to append the rows to an existing table, then link to the spreadsheet and run an append query to copy the data from the spreadsheet to the server.

I really hope that you are not creating a new table for each spreadsheet. That would be an unmanageable design.