copy Ms access single Big Table to Sql server two small tables

Question

Dear friends,

I have single big table in Ms access 2k daabase and i need a way to copy this table and then populate my two already ready tables on sql server .

I can use Migration tool but is there any way we can do it from Ms access like a form which executes the stored procedure or ODBC connection.

what i am thinking is to create a form in ms access which should have a browse button for selecting my source access mdb file and then another button for processing[on clicking the table specified above by browse button should be imported into sql server]?? Is this possbile? please give me some details

Thanks in Advance

Answer 1

I have single big table in Ms access 2k database and I need a way to copy this table and then populate my two already ready tables on SQL server .

How big is "big"? SQL Server should be able to handle any single table you would ever store in Access. I don't think a terabyte of data would be unfeasible.

You could use Microsoft SSIS. Or just dump a .csv representation of the Access database and import it into SQL Server. Or upsize Access to SQL Server.

Your question is confusing to me. Are the two "already ready" tables in addition to this one big table, with other data to be stored in them? I certainly hope that they don't have the identical layout as the "big" table in Access (violating first normal form) and encourage you to divide the data from Access between them, thinking that it's an improvement of some kind.

Answer 2

You could create an Access Project (.adp) to the Sql Server and then use the File>Import menu in MS Access (in the .adp) to import your table into the (.adp) and thus Sql Server.

Then just use a simple SELECT INTO stored procedures to populate your tables in Sql Server from the imported table.

Answer 3

The following is some code that you can use to generate a DSNless link to a table in a SQL Server database from Access. It works, at least, in Access 2003. After you have run this code, the SQL tables can be manipulated directly just like any other Access table.

'Name     :   AttachDSNLessTable
'Purpose  :   Create a linked table to SQL Server without using a DSN
'Parameters
'     stLocalTableName: Name of the table that you are creating in the current     database
'     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'     stServer: Name of the SQL Server that you are linking to
'     stDatabase: Name of the SQL Server database that you are linking to
'     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, _
                        stDatabase As String, _
                        Optional stUsername As String, Optional stPassword As String)

    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        'Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        'WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:

     AttachDSNLessTable = False
    MsgBox "Error while trying to link to SQL Server on " & stServer & ": " & Err.Description

End Function

Answer 4

You could set up table links to the two SQL server tables from inside MS Access.

Then you could create a query that populates each table from the source table.

Answer 5

I suspect that running an INSERT from Access with ODBC linked tables would be very slow, as Jet/ACE tends to break down each row into a separate INSERT statement. Jet/ACE is being a good citizen user of the mult-user server database, since this allows the server to serialize operations generated by multiple users and interleave others updates in with the massive update. But it's horridly slow for large datasets, and when you're doing something like you are, which is initializing an empty dataset, it's being too "civically responsible."

You might consider renaming the existing table, and instead exporting the Access table up to SQL Server via an ODBC DSN. If you have the DSN already defined, you just choose EXPORT from the FILE menu in Access, choose the DSN as a destination, and Jet/ACE takes care of the rest, creating the table on the server and uploading all the data. It's very efficient (i.e., won't do it one record at a time) since Jet/ACE knows it's populating a table that didn't previously exist.

Now, the result might not be exactly what you like (Jet/ACE might not get all the data types right because of the translation aspects of ODBC drivers, but the data types should be compatible, if the the exact strictest data types desired), but you will then have your full dataset in you SQL Server database, and can then append from one SQL Server table to the correctly structured one.

You would want to do a check of the data to make sure no data has been lost or incorrectly transformed (an example of this would be a text zip code field getting converted to a number -- this would actually never happen, but it's the kind of thing you'd want to check for), but I'd think for a 500MB dataset when you don't have good upsizing tools (because the Access versions can't keep up with the recent SQL Server versions), this might get the job done more efficiently with less work.