Copy SQL table to Access from Word vba

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Flemming Dahl - 04 Apr 2005 19:57 GMT

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

Reply to this Message

Perry - 04 Apr 2005 23:13 GMT

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

> Hi all,
>
[quoted text clipped - 11 lines]
> Thanks,
> Flemming

Reply to this Message

Flemming Dahl - 05 Apr 2005 14:25 GMT

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.

> Ask your SQL server database administrator to prepare a Stored Procedure for
> you one to export the data to your
[quoted text clipped - 33 lines]
> > Thanks,
> > Flemming

Reply to this Message