Refreshing SQL Database

[Ernst Guckel]

Hello,

I have an access database that needs to get data from an SQL Database.
There are local tables that will have relationships with the SQL Database
tables. I can create a saved import that will go get the tables and copy
them to the local database but this has issues with out process.

I want to create a function that will delete the local copies of the SQL
database tables and then refresh the data again.

1. I'm concerned that if I create relationships then delete and recreate
the tables the relationships will be broken.
2. And how to copy the table from the SQL Database in code. It needs to
get all tables that meet certain naming criteria. IE tbl* ...

Any help would be great.

Thanks,
Ernst.

 

[Jeff Boyce]

Ernst

Here's an alternative approach ...

Instead of deleting the local tables entirely, what about the idea of
removing all the records? Then you could just append all the records from
the SQL database table.

If you created a query that does the append for one table, you could call
that query in code.

(by the way, I'm curious... what business need are you solving by having a
duplicate local copy of data that's already on your SQL Server?)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Ernst Guckel]

Thanks for the reply...

There are some users that we would prefer not have access to the source
data... They have no need to change it.

I have worked out the code to do what I want but now when i link to a table
( FOr those who need live access to the data) I get #DELETED in all the
fields... I have read about there being this problem with Access and the
BigInit datatype as the PK but is there a work around?

Ernst.

 

[Jeff Boyce]

Ernst

I've not run into that issue, so I have no first-hand experience. Perhaps
another reader has?

(by the way, again, if your data is in SQL-Server, you can use the security
in SQL-Server to prevent users from updating the records stored there!)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.