Copy SQL table to Access from Word vba

[Flemming Dahl]

Hi all,

I have a word application that uses a local Access database. This Access
database I need to updated from a SQL server.

I would like to copy a table from the SQL to the access database like this:
- all data in the Access database are lost
- the SQL table are copied to the Access database, not row by row but simply
copied (this I belive should give more speed)

Can anybody point me in the right direction or even better show me some code
that works

Thanks,
Flemming

 

[Perry]

Ask your SQL server database administrator to prepare a Stored Procedure for
you one to export the data to your
Access database.
You can use automation to delete the table in Access prior to calling the
(note: server side) Stored Procedure.

Here's some sample/pseudo code of how to run a stored procedure off VBA,
whereby
"adCmd" is a valid/instantiated ADO command object and "cn" a
valid/instantiated connection object.

adCmd.CommandType = adCmdStoredProc
adCmd.CommandText = "myStoredProcedureName"
Set adCmd.ActiveConnection = cn
Refresh adCmd

Krgrds,
Perry

 

[Flemming Dahl]

Hi Perry

Thank you for your answer. Today I have found a SQL specialist within our
company, and he said that he could help me.

The access database is found on 6000 pc's world wide so speed is a big
issue.

Thanks for you time.
Flemming

Question is now closed.