Looking for a shortcut to convert Access table/s to SQL.

Hi Experts,
I'm looking for a shortcut to convert Access table/s to SQL.
Table have dozens of fields, many with long names (no illegal names like spacing etc..).
Everything must stay the same, no re-naming, changing types, organizing etc.. otherwise FE app will fail.
What is the easiest way?
LVL 5
bfuchsAsked:
Who is Participating?
 
Nitin SontakkeDeveloperCommented:
I am not an MS-Access expert. Used to work with it years ago, so might have forgotten few things.

If you use Import / Export wizard within MS SQL Management Studio (it's actually an SSIS package!) you can get all your tables in SQL Server. To my knowledge Access doesn't have a way to specify text length and hence all your text columns get migrated with nvarchar(255) flat.

If that is true, you in any case of have a task of identifying correct length for each column and modifying it manually in any case.

And if that is true, I suggest use Import / Export wizard, get the table structure in SQL Server, script it, modify things as appropriate in script itself and then re-run the script and then import the data.

Depending on how fluent you are in VBA and enthusiastic about the whole process, second approach would be to create metadata in MS-Access tables itself and write VBA scripts to generate SQL Scripts from MS-Access meta-data. Sounds complicated, but still doable and will give you more control and you will be able to perform several runs (should you have to!) of the process.

Remember, EVERYTHING is data!
0
 
PatHartmanCommented:
Shortcut?  If you have A2010 or earlier, the upsizing wizard will do the entire job for you.  Otherwise, you are stuck with SSMA which last time I tried it was a big problem.  For some reason MS elected to not provide a 32-bit version of SSMA and since the vast majority of Access apps are created with the 32-bit version of Office, there is no easy solution.  Following the MS directions to install the Access runtime, hosed my Access installation so I don't recommend doing that unless you have a spare machine you can play with.

Needing to do this in a hurry a couple of weeks ago, I through together some code that you might be able to work with.  It doesn't transfer the data.  All it does is create DDL.  So far all the DDL I have created works but the code has not been extensively tested.

Public Sub RecreateTableInSQLServer(TableName As String)
On Error GoTo Err_RecreateTableInSQLServer
'this routine accepts a table name, reads the definition and creates a Create Table
'statement that can be used to recreate the table in SQL Server'
'Pat Hartman 5/1/18

    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim idx As DAO.Index
    Dim strSQL As String
    Dim indexfields As String
    Dim indexunique As Variant
    Dim path As String
    Dim notnull As String

    Set tdf = db.TableDefs(TableName)
    path = (Mid(db.Name, 1, Len(db.Name) - Len(Dir(db.Name))))

    'find the primary key fields
    For Each idx In tdf.Indexes
        If idx.Primary = True Then
            indexfields = idx.Fields
        End If
    Next

    If Me.txtDatabaseName & "" = "" Then
        strSQL = ""
    Else
        strSQL = "Use [" & Me.txtDatabaseName & "]" & vbCrLf
        strSQL = strSQL & "GO" & vbCrLf
    End If
    strSQL = strSQL & "CREATE TABLE " & Me.txtOwnerName & ".[" & TableName & "] ("

        For Each fld In tdf.Fields
            If InStr(indexfields, fld.Name) > 0 Then
                notnull = "NOT NULL"
            Else
                If fld.Required = True Then
                    notnull = "NOT NULL"
                Else
                    notnull = ""
                End If
            End If

            Select Case fld.Type
            
            '' see http://allenbrowne.com/ser-49.html  -- for a list of field types
            
                Case 4  'Long Integer or Autonumber field
                    If fld.Attributes = 17 Then
                        strSQL = strSQL & "[" & fld.Name & "] INT IDENTITY, "
                    Else
                        strSQL = strSQL & "[" & fld.Name & "] INT " & notnull & ", "
                    End If
                Case 10  'Text field
                    strSQL = strSQL & "[" & fld.Name & "] VARCHAR(" & fld.Size & ") " & notnull & ", "
                Case 12  'Memo field
                    strSQL = strSQL & "[" & fld.Name & "] NTEXT " & notnull & ", "
                Case 2  'Byte field
                    strSQL = strSQL & "[" & fld.Name & "] TINYINT " & notnull & ", "
                Case 3  'Integer field
                    strSQL = strSQL & "[" & fld.Name & "] SMALLINT " & notnull & ", "
                Case 6  'Single-precision field
                    strSQL = strSQL & "[" & fld.Name & "] REAL " & notnull & ", "
                Case 7  'Double-precision field
                    strSQL = strSQL & "[" & fld.Name & "] FLOAT " & notnull & ", "
                Case 15  'ReplicationID field
                    strSQL = strSQL & "[" & fld.Name & "] UNIQUEIDENTIFIER " & notnull & ", "
                Case 8  'Date/Time field
                    strSQL = strSQL & "[" & fld.Name & "] DATETIME " & notnull & ", "
                Case 5  'Currency field
                    strSQL = strSQL & "[" & fld.Name & "] MONEY " & notnull & ", "
                Case 1  'Yes/No field
                    strSQL = strSQL & "[" & fld.Name & "] SMALLINT " & notnull & ", "
                Case 11  'OleObject field
                    strSQL = strSQL & "[" & fld.Name & "] IMAGE " & notnull & ", "
            End Select
            strSQL = strSQL & vbCrLf
        Next fld
    If Me.chkAddDateStamp = True Then
        strSQL = strSQL & "[upsize_ts] [timestamp] Null, "
    End If
    strSQL = Left(strSQL, Len(strSQL) - 2) & ");"

    Open path & "Create_" & TableName & ".sql" For Output As #1
        Print #1, strSQL
        Print #1, ""

    For Each idx In tdf.Indexes
        indexfields = idx.Fields
        indexfields = Replace(indexfields, "+", "")
        indexfields = Replace(indexfields, ";", "], [")
        'indexfields = "[" & indexfields & "]"
        If idx.Unique = True Then
            indexunique = " UNIQUE "
        Else
            indexunique = Null
        End If
        strSQL = "CREATE " & indexunique & " INDEX [" & idx.Name & _
            "] On [" & TableName & "] ([" & indexfields & "]);"

        If idx.Primary = True Then
            strSQL = "ALTER TABLE [" & TableName & _
                "] ADD CONSTRAINT [PK_" & TableName & _
                "] PRIMARY KEY ([" & indexfields & "]);"
        End If

        Print #1, strSQL
        Print #1, ""

    Next

    Close #1
    Set idx = Nothing
    Set tdf = Nothing


Exit_RecreateTableInSQLServer:
    Exit Sub

Err_RecreateTableInSQLServer:
    MsgBox Err.Description
    Resume Exit_RecreateTableInSQLServer
    Resume
End Sub

Open in new window

The code runs from a form.  Hopefully, you can figure out the form field references.
CreateDDLForm.JPG
0
 
John TsioumprisSoftware & Systems EngineerCommented:
SSMA has a 32bit version ...by popular demand.....is back
So you pick it up and let it do the conversion....
Also besides SSMA there is also Bullzip's Access to MSSQL if you just want to upload your Access tables to SQL
The problem however is not moving your Access data to SQL ...this is the easy part...you just a pick an application that does the job (there are some more if you google around) ...the problem arises just after you done the move....
While SQL is more than great is not the magic pill that will resolve your issues...if your database right now is badly behaving it will continue to behave badly and even worse....expect a lot of waiting/timeouts /errors...believe me i have done extensive testing on it ....and MSSQL is the "good" guy...if you choose MySQL,Oracle,PostGre,DB2....probably you will start screaming...
Access+SQL is a great combination (a MVP calls it 'made in heaven') ...but only if you respect it...if you do your homework ...read the articles and do your testing...
Lets say this : i was working in a company...running a huge Access application ...talking about 1000+ tables/queries....200K lines of code...at some time i dropped the idea of migrating BE to SQL....the management agreed and start testing....after some days...i informed the management that it would be great choice to make but not without sacrifices...i estimated that i needed around 6+ months to make all the necessary changes to the all the tables/queries in order to make a successful migration...one that would really take advantage of SQL...the management dropped it on the spot....now i have long gone ...they are still on Access..
1
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ben,

   Just wanted second that using SSMA is your best bet as it allows for things like adding a Timestamp field to all your tables (and you need to do this).

  Adding a Timestamp field in SQL turns on row versioning for the table and this is critical for Access working with the tables.

  As John said, the conversion is really just the start.  While most apps will do OK with just that, some do not, so it's important to test carefully before you cut-over.

Jim.
0
 
John TsioumprisSoftware & Systems EngineerCommented:
To add to my previous along with Jim's comment...migrating is just the easy part...a couple of mouse clicks...SSMA even relinks your tables with SQL ...and at first everything would be just fine...
Probably some of your forms would work right away and hooray ...we have SQL as BE...you can even post it on the company's Blog/Linkedin/Facebook ...you name it
(i know it from first hand that's why i am mentioning it...:)  )
And then you decide to open that "stupid" table that carries some hundreds of thousands records (you know that log/history tables )...and oops ...the applications froze...it might recover might not..
Then the users start complaining...these comboboxes just don't work as they should...
Again that update query that usually was a few seconds to execute now timeouts...
MsSQL ODBC driver does its best to hide all these issues....in cases with limited records you might not even notice it...even on larger tables everything seems OK.............i am mentioning that because ODBC for other engines are just not so efficient.
So what't the "medicine"....well is not an easy task because you must forget the ease of Access that you just double click and you get everything on the plate...now passthrough queries is your best friend and ADO just has to replace that nice DAO...the philosophy is now....you get only what you want ...nothing more nothing less....
As you move along you will find the true power of SQLs executing queries that would take ages in split second ...but only if you talk directly skipping the middle man (Access <--> Linked Tables)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<ADO just has to replace that nice DAO...the philosophy is now....you get only what you want ...nothing more nothing less....>>

 Actually, for dealing with SQL server, Microsoft has gone back to DAO/ODBC as the best choice.

 While ADO seems more flexible (and in some cases it is), if you check, what you'll find is that very often you'll get handed back a cursor type that is entirely different than what you requested.   Going even further in checking, you'll find that almost the same thing gets returned in just about every case (see below).

Jim.

' ADO Cursor types (lighest to heavyest)
' adOpenUnspecified -1 Unspecified type of cursor
' adOpenForwardOnly - 0 A forward-only cursor. Cannot move backward.  If you do, recordset is closed and source requeried.
' adOpenStatic - 3 A static copy of a set of records. Additions, changes, or deletions by other users are not visible.
' adOpenKeyset - 1 Static snapshot of PK values.  As you scroll through records, records are fetched.  You can see updates made by other users, but not new records added.
' adOpenDynamic - 2 All changes by other users are visible.  This cursor type however is not supported by the JET OLEDB provider.

' Lock types
' adLockUnspecified - -1 Unspecified type of lock. Clones inherits lock type from the original Recordset.
' adLockReadOnly - 1 Default. Read-only records
' adLockPessimistic - 2 Pessimistic locking, record by record. The provider lock records immediately after editing
' adLockOptimistic - 3 Optimistic locking, record by record. The provider lock records only when calling update
' adLockBatchOptimistic - 4 Optimistic batch updates. Required for batch update mode

' Resulting cursor type based on type requested and lock type for JET OLE DB Provider:
' adOpenForwardOnly/adLockReadOnly - Forwardonly
' adOpenForwardOnly/adLockPessimistic - Keyset
' adOpenForwardOnly/adLockOptimistic - Keyset
' adOpenForwardOnly/adLockBatchOptimistic - Keyset

' adOpenStatic/adLockReadOnly - Static
' adOpenStatic/adLockPessimistic - Keyset
' adOpenStatic/adLockOptimistic - Keyset
' adOpenStatic/adLockBatchOptimistic - Keyset

' adOpenKeyset/adLockReadOnly - Keyset
' adOpenKeyset/adLockPessimistic - Keyset
' adOpenKeyset/adLockOptimistic - Keyset
' adOpenKeyset/adLockBatchOptimistic - Keyset

' adOpenDynamic/adLockReadOnly - Keyset
' adOpenDynamic/adLockPessimistic - Keyset
' adOpenDynamic/adLockOptimistic - Keyset
' adOpenDynamic/adLockBatchOptimistic - Keyset

' NOTE TO ABOVE:
'  Command type of adCmdTableDirect always yields a Keyset cursor.
'  Command type of adCmdText and adCmdTable yield types above
'  Requesting a client side cursor always gives you a Static cursor.
1
 
John TsioumprisSoftware & Systems EngineerCommented:
Interesting i have to check it...i always use ADO when i deal with SQL ...do you have some more info ?
0
 
PatHartmanCommented:
I didn't have a lot of time when I posted originally and thought it was more important to give you the code to generate the DDL in case you have a problem with SSMA - I downloaded the latest 32 bit version BTW and it still won't run without installing something else so I'm not going to take the chance again of following the bad instructions.

As the others have mentioned, converting the tables to SQL server is pretty easy (it used to be trivial but not any more due to MS dropping the upsizing wizard).  But if you don't understand client/server processing and your Access app is developed using old style techniques with forms bound to tables and relying on filters to whittle down the recordset, you are going to be sorely disappointed in the performance.  SQL Server will almost certainly be slower than Jet/ACE if your tables are even moderately sized.  I've been using Access linked to SQL Server, Oracle, DB2, etc for over 20 years.  As long as your forms are bound to queries that return a very limited set of records, you can usually get by by not changing anything else except some DAO code which will need the dbSeeChanges argument.  When I experience slowness, it can usually be corrected by tweaking the indexes or adding a View to make joins more efficient.  In a couple of cases, I've built stored procedures to gather data for complex reports.  I also use pass through queries for bulk deletes.  If I am deleting all the rows in a table I use Truncate rather than Delete.

So, it isn't necessary to start believing that you need to convert all queries to stored procedures or to use unbound forms.
0
 
hnasrCommented:
Some options are available when you search for "convert Access table to SQL"
0
 
bfuchsAuthor Commented:
Hi Experts,
I was not in the office today, hope to be there tom & get to test those suggestions.
Thanks for all replies.
Ben
0
 
Bharat BhushanSolution ManagerCommented:
Not sure what you mean by a shortcut.  Are you saying just double-click and convert?  If yes then there is no way.  Whether you import the tables from Access to SQL or export them from Access to SQL it has to go thru the check to make sure they are compatible.
0
 
bfuchsAuthor Commented:
Hi Experts,


I ended up using the following, and very happy with the outcome.
If you use Import / Export wizard within MS SQL Management Studio (it's actually an SSIS package!) you can get all your tables in SQL Server.

Thanks to all participants.
Ben
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.