Need to connect to a SQLServer2K DB from Access2K!

[pompeyjon]

Hi All,

My situation is thus: I have a SQL2K database that I need to access from Access2K. Now I know I could setup lots of linked tables using ODBC, but it is essential that 2-way access to the SQL2K database is not in effect.

I was thinking of designing a macro that would first make a copy of the SQL2K tables, then on subsequent visits, truncate the tables then copy them again (so as keep the records in the copied tables current).

Only trouble is, I don't know how to write a query in Access that will enable to directly copy a SQL2K table. Can anyone help?

Regards,

Jon

 

[lynchg]

You would have to at least temporarily create a link to your SQL Server tables in order to make a copy of them into Access, which you could do with code.

The syntax for creating a new table and copying the data from another table into the new one is this:

Select OrigTable.Field1, OrigTable.Field2,... Into CopyTable From OrigTable;

You need to be concerned about the amount of data you are pulling into Access from SQL Server, Acces can't handle nearly the same volumes. Another concern is the frequency of the 'updates' into your local copies, the data in your local table could be out of date almost as soon as it is created.

These can both be eliminated by having linked tables, I wonder why that would be a problem.

 

[pompeyjon]

I know that linked tables would solve my problem - however, the user in question doesn't seem to understand that linked tables are still "live" - we have had situations where records have been deleted, etc.

What I really need is a way of executing an SQL query on the SQL2K database, but using the Access2K client.

What I want the user to do each time they open the Access2K database is:

1) Run query to delete copied tables in Access2K.
2) Run query to copy over current SQL2K tables.
3) Execute their own queries on the table copies.

Jon

 

[ehsguy77]

Can you make different forms for your linked SQL tables that are read only? We've done that for several of our forms - we just set the properties of all the fields to not enabled.

 

[cmmrfrds]

If this is an sql server database then don't give the users write permissions on the tables. In sql server it is easy to setup various security requirements.