Uploading Access to SQL Programmatically (1 Viewer)

[supercharge]

Hello to all the experts out there. Hope you're having a great day.

Does anyone know how to upload (append) programmatically a table in Access to an existing table in SQL Server 2000?

On my VB app, there's an Upload button that when clicked will do the above task. The Access database also requires a password to login. I searched around and saw that some people used Linked Servers to create a virtual server for the Access database. Is this the only way?

Thanks all in advance.

Extra info:
Access 2000
VB 6
SQL Server 2000

 

[FoFa]

Usually I link the SQL table to my access application, then append to that.
I also usually use a SQL id embedded in the link table information so they do not have supply an ID.
Just my 2 cents worth.

 

[supercharge]

Thanks FoFo for the response.

I'm trying to avoid going through Access because that's not needed and would create an extra step. There should be a direct way to go from VB to SQL Server.

How about something like this (brainstorming), will this work?

1.  Open connection to Access DB
2.  Loop thru each record
    A.  Copy first record
    B.  Open connection to SQL DB
    C.  Create a new record in SQL table
    D.  Paste record as a new record
3.  Close connection to SQL 
4.  Close connection to Access

Thanks

 

[FoFa]

You can use ADO an either use insert statments or update record set code, either will force you to loop through the main recordset however.
Another possibility, but tougher to get security correct, is to create a SQL DTS job to handle the import (if it is a static source) and have your VB program start a SQL job to handle the DTS job. Then it would all be done in the background.

 

[supercharge]

Man! what happend to this forum? After leaving it unactivated for several minutes, it kicked me out and I'll have to login in again. Oops, back to business.

Thanks FoFa. As I remember, for every connection, only one action can be performed. For example:

        RecordSet.Open sqlStatement, connection
        txtField = RecordSet.GetString

        'Have to close and open again for another action
        RecordSet.Close        

        RecordSet.Open AnotherSqlStatement, connection
        Me.txtField2 = RecordSet.GetString
        RecordSet.Close

Will my process be really slow for opening and closing too many connections?

Thanks

 

[FoFa]

Typically you will open your input recordset and loop through it.
Open recordset to rs1
rs1.movefirst
while NOT rs1.EOF
LOOPING CODE
rs1.movenext
loop
rs1.close

And it usually works fairly quick with ADO