Hi.
how do I write a code to bypass that password to have the table copied over?
There are several ways to do this. The simplest is to write SQL queries.
Since this is an "autorun" unattended application, disable the "Confirm
action queries" option to prevent prompts from stopping execution of the
queries.
Do you really need to replace the table (does it have a different structure
than the new table)? If so, then drop the current table in the remote
database, then run a make table query to create the new one in the remote
database, then run VBA code that creates the primary key and any indexes for
the new table so that it matches the source table.
Or do you just need to replace the records in the table in the remote
database (the table structure remains the same)? If so, run a delete query
to remove the old records, then run an append query to insert the records
into the remote database.
Example syntax for dropping a table in a remote database that has a database
password:
DROP TABLE [;DATABASE=C:\Test\Data.mdb;PWD=myPassword].tblStuff;
Example syntax for a make table query for a remote database that has a
database password:
SELECT tblStuff.*
INTO [;DATABASE=C:\Test\Data.mdb;PWD=myPassword].tblStuff
FROM tblStuff;
Example syntax for deleting records in a table in a remote database that has
a database password:
DELETE *
FROM [;DATABASE=C:\Test\Data.mdb;PWD=myPassword].tblStuff;
Example syntax for appending records in a table in a remote database that
has a database password:
INSERT INTO [;DATABASE=C:\Test\Data.mdb;PWD=myPassword].tblStuff
SELECT *
FROM tblStuff;
These SQL statements can be incorporated into your VBA code, then the
database converted into an MDE database file for distribution so that the
source code, including password, cannot be viewed (recommended method), or
these queries can be created in the unsecure database, but the database
password will be visible to anyone who looks.
For more information, please see the links to "How to use Jet SQL to insert
records into a remote database that has a database password" and "How to use
Jet SQL to update records in (or from) a remote database that has a database
password" in the "Queries" section on the following Web page:
http://www.access.qbuilt.com/html/how-to_tips.html
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.)
- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.
