Hi, Siggi.
I imagine that you aren't linking directly to the SQL Server table because
you don't want the Access users to be fiddling with the production version of
the data. If that's not the case, then just linking to SQL Server via a DSN
would be the easiest way for the Access users to "see" the SQL Server data.
To keep the records in Access in sync with the records in SQL Server, just
delete the records first from the Access table, then append the records from
the SQL table with a query. It would be easiest to create a temporary link
to the SQL Server table and append the records to the Access table with a
simple append query, then delete the link. For example, these two queries:
DELETE *
FROM tblAccess;
INSERT INTO tblAccess
SELECT *
FROM tblSQLServer;
.. . . where tblAccess is the name of the local table in Access,
tblSQLServer is the name of the linked table.
Please see the following Web page for VBA code to relink the tables:
http://www.mvps.org/access/tables/tbl0009.htm
If you can't link to SQL Server, then you may still use queries to get the
data from SQL Server, either through a query or via VBA code. For example,
if the data were in another Access database, then the following VBA would
work:
sqlStmt = "DELETE * " & _
"FROM tblAccess;"
CurrentDb().Execute sqlStmt, dbFailOnError
sqlStmt = "INSERT INTO tblAccess " & _
"SELECT *
"FROM [;DATABASE=C:\Work\OtherDB.mdb;].tblInOtherDB;" & _
CurrentDb().Execute sqlStmt, dbFailOnError
.. . . where tblAccess is the name of the local table in Access,
tblInOtherDB is the name of the table in the other database, and
C:\Work\OtherDB.mdb is the path to the other Access database.
However, since you're using SQL Server, you'll need a SQL Server connection
string inside the brackets of the FROM clause. If you need help with the
connection string, then please see the following Web page for examples:
For a DSN-less connection:
http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForSQLServer
For a DSN connection:
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSN/tabid/89/Default.aspx
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
