export table to sql server

[qwertyjjj]

I need to export a table from Access to SQL server by using code.
Ideally, I'd just like to leave the table on SQL server but the network is very slow here so this is about the only way to do it.
Export the table and then have it accessed by other applications on the SQL server?

 

[Guus2005]

I need to export a table from Access to SQL server by using code.
Ideally, I'd just like to leave the table on SQL server but the network is very slow here so this is about the only way to do it.
Export the table and then have it accessed by other applications on the SQL server?

Add an "updated" field containing a timestamp to the table.
Write a small sync procedure which copies/overwrites the updated records and adds the new ones.

That's it.

 

[qwertyjjj]

So far, I have this:
Private Sub Command23_Click()
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
'Dim rs As ADODB.Recordset
Dim sConnString As String
Dim iCtr As Integer

sConnString = "Provider=SQLOLEDB.1;User ID=sa;password=putpasswordhere.;Initial Catalog=CCApp;Data Source = 10.10.10.101;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

conn.Open sConnString
Set cmd.ActiveConnection = conn

'REPLACE MYTABLE WITH YOUR OWN TABLE
cmd.CommandText = "SELECT * FROM MYTABLE"
cmd.CommandType = adCmdText
'Set rs = cmd.Execute

'Do While Not rs.EOF
' For iCtr = 0 To rs.Fields.Count - 1
' 'OutPuts Name and Value of each field
' Debug.Print rs.Fields(iCtr).Name & ": " & _
' rs.Fields(iCtr).Value
' Next
'rs.MoveNext
'Loop
'Set rs = Nothing
Set cmd = Nothing
conn.Close
Set conn = Nothing
End Sub

But it needs to know that I am updating the SQL server table with a table from Access.
How do you get that into the SQL here, otherwise it will think I am referring to the tables on the SQL server alone?

 

[Guus2005]

oOoh.

Different approach:

connect the SQL Server table: TARGETTABLE to Access.
Lets say that the name of the Access table you wish to export is called SOURCETABLE.
The definition of both tables are the same.
Clear the TARGETTABLE.
run Query

insert into targettable (...,...,...) select ...,...,... from sourcetable

Now you have updated the SQL Server table TARGETTABLE.

Bottomline is that you have to attach the SQL Server table to access and fill it as if it was a local table. Use this procedure every hour or something like that.

 

[qwertyjjj]

But if the Access DB is being used on a different machine all the time, it won't be able to read the DSN file on my machine will it?
So it has to be a DSN less connectin somewhere and then link the tables.

...or not?

 

[Guus2005]

Oke, quick and dirty doesn't work.
You have to update only the records that where changed since the last update.
Retrieve the last timestamp, run a query on the local table using the timestamp.
Now you have the records which you must synchronize with your SQL Server table.

You already have a connection with your database. Open the table like you did. Select the record that must be changed. Change it. Do this for every record which was changed.

Enjoy!

 

[qwertyjjj]

No, the queries work when I add a linked table.
What I mean is that the linked table uses a DSN file on my computer to know which server to connect to.

On the 20 other client PCs that will be using this, there is no DSN file, so the application will fall over. I need another way of specifying the SQL server and refreshing the linked tables everytime the application is started don't I ?

 

[Guus2005]

Connecting to a SQL Server database without DSN. Not sure if that is possible.

Workaround: Use a pull mechanism instead of a push mechanism. Use DTS to retrieve the info from all your access databases.

 

[qwertyjjj]

Surely you can, no ?
The code at the top of this post uses a connection string to connect to SQL in VB.
I'd just need a way of importing the data from the Access table to SQL.
It works with linked tables fine but that uses a DSN stored on my computer.

So, either:
- connect with a connection string and do the same as I am doing now...not sure how to;
- add all the dsn connections into VBA and have it add them everytime it is installed on a client machine.

Would that work?

I can't use DTS to pull as the Access DB will be in a different place everytime. It's not stored on the network