Fastest Method to Push Local Access Table to SQL Server table.

[SteveApa]

Hi All.
What is the fastest method to push a local access table to an sql server table. I am programming this application for global users. I tried looping thru each recordset (code below) and also tried a local access append query to a linked sql server table. Both methods seem to run at the same speed - slow. For the VBA method, I looped thru with a DAO local recordset and ado sql server recordset. Both tables have the same record fields.

    'progress bar
SysCmd acSysCmdInitMeter, "Writing to Server...", rs_dao.RecordCount
    j = 1
    While Not rs_dao.EOF
        rs_ado.AddNew
        For i = 0 To rs_ado.Fields.Count - 1
            Select Case rs_ado(i).name
            Case "ElementID"
                'do nothing this is a auto number / primary key created on sql server
            Case "Publish Date"
                rs_ado(i) = Now()
            Case Else
                rs_ado(i) = rs_dao(rs_ado(i).name)
            End Select
        Next i
        rs_dao.MoveNext
        j = j + 1
        If j Mod 10 = 0 Then
SysCmd acSysCmdUpdateMeter, j
            DoEvents
        End If
    Wend
SysCmd acSysCmdRemoveMeter
    rs_ado.UpdateBatch
    rs_dao.Close
    Set rs_dao = Nothing
        
    rs_ado.Close
    Set rs_ado = Nothing

Thanks for the help.
Steve
Harrisburg, PA

[ranman256]

put the table, or the data?
if data , run an update query. (not using vb to cycle thru records 1 at a time)
if table design, you must enter sql manager and design from scratch.

[Minty]

If you mean copy data from Access > SQL then an update or append query is the fastest way, but it will be slow over a WAN if you are doing 1000's of records.
If there are a lot of indexes on the destination table, it may be quicker to write to a temporary empty SQL table with no indexes then call an SP to update from that to the main table?

Certainly as Ranman has stated, looping one record at a time will definitely be really slow.

Is this a one off or continuous process?

[isladogs]

Answering your question literally, you can't push an access table to SQL Server
You need to create the table in SQL Server either in SSMS or using VBA code in Access then transfer the data using an INSERT (append) query.
If the SQL table already exists, use either an update or append query as appropriate.

BUT if the SQL table does already exists, make it a linked table in Access so that records can be added/edited directly from Access

[NoellaG]

Fasted method: design a SSIS package or use the import wizzard in SQL server, never ever use RBAR (Row By Agonizing Row). If the import needs to be done on a regular base, you can add the package to a SQL job.