Export from Ms Access to SQL Server (1 Viewer)

A

abenitez77

Registered User.
Local time
Today, 11:48
Joined
Apr 29, 2010
Messages
141
is there a way to export from ms access to SQL Server using vba code?
 
isladogs

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,209
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
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
43,226
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

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,209
Pat Hartman said:
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.
Click to expand...

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

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:48
Joined
Feb 28, 2001
Messages
27,142
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.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:48
Joined
Jan 20, 2009
Messages
12,851
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

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,209
Galaxiom said:
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.
Click to expand...

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.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
43,226
You can use the upsizing wizard that is built into Access ...
- Database Tools ... Move Data .. SQL Server
Click to expand...
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

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,209
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?
 
A

abenitez77

Registered User.
Local time
Today, 11:48
Joined
Apr 29, 2010
Messages
141
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 :

Code: 
 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

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,209
First thing is to check the datatypes in the table to see if any are not supported in SQL Server
 
isladogs

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,209
Have a look at this code with a variety of connection strings & see if it helps

Code: 
'##########################################
' 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
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 19, 2002
Messages
43,226
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.
 
You must log in or register to reply here.

Similar threads

N
Insert Data from an Access table to a similar table in MS SQL Server
2
Replies
36
Views
577
cheekybuddha
cheekybuddha
N
Solved JAR/WAR replacing Winsock MS Access VBA
Replies
2
Views
249
sonic8
S
R
using sql server over a network
Replies
9
Views
574
KitaYama
K
J
Check for presence of an SQL Server
2
Replies
20
Views
796
JohnPapa
J
J
Create DSN-less connection to SQL Server
2 3
Replies
48
Views
1,953
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom