SQL Server BackEnd and Automation

[Treason]

I have a VBA script that opens a intra net web page and downloads data from it. The data is stored locally in Access tables. After the tables are filled, the script ftp's the database to the web. This script is set to run every night at midnight and has worked well for months now.

I had to upgrade my website's database to MSSQL server from MS Access. So now my script needs to be modified. I need to take these access tables and upload them to SQL Server instead of a simple ftp upload.

In SQL management studio I can import an access database easily. It transfers all of the data in 5 minutes or so. But I have to do this manually and click through a wizard.

I have tried setting up linked SQL tables and running append queries with VBA using DoCmd.RunSQL This method is way too slow. I tried saved append queries using query design grid and it is only a little faster.

I am transferring 10 tables or so, with anywhere from 1000-100,000 records. I've read through 3 pages of SQL Server searches and I am still at a loss. I really need to automate this process.

Any help is greatly appreciated as always...

 

[Treason]

Well I answered my own question,

The quickest method was transfer database:

Here's my example:

Sub ExportMSSQL(DBN As String)
On Error Resume Next
    DoCmd.SetWarnings False
    'Drop tables
    Dim Conn As New ADODB.Connection
    Set Conn = New ADODB.Connection
    Conn.Open "DSN=myDSN", "myuid", "mypass"
        Conn.Execute "DROP TABLE [mytable].[table1]"
    Set Conn = Nothing
        
    'Transfer
    DoCmd.TransferDatabase acExport, "ODBC", "ODBC;DSN=myDSN;Description=blah;UID=myuid;DATABASE=MyDB;PWD=mypass;", acTable, "Localtable1", "table1", False
    

End Sub

Of course you have to make a file or system DSN first. And I got the connection string by creating a linked table to MSSQL and then going into design view, then table properties.

 

[Guus2005]

Thank you for the feedback!
Much appreciated!