Append Queries

  • Thread starter Thread starter Nick1966
  • Start date Start date
N

Nick1966

Hi

I have created an Append query to update records from a table to another
table in a separate database, however the query only works if I remove the
password from the database that I am appending to, is there a way around
this problem.

Thanks


Nick
 
Nick1966 said:
Hi

I have created an Append query to update records from a table to
another table in a separate database, however the query only works if
I remove the password from the database that I am appending to, is
there a way around this problem.

Thanks


Nick
Click to expand...

You should be able to do this and maintain security by switching to user
level security, making sure both databases use the same workgroup and that
the user has the needed security on both databases.

There may be some way of doing it using code, but I don't know. The
Access password security system is rather weak.
 
No the tables are not linked currently, the table containing the source data
allows multiple entries for the field that I need to append to the external
table. I thought this may cause problems appending the data, especially as
one field is the Primary Key field in the External database. Also the append
query only needs to be run maybe every 6 weeks or less if not required.

Nick
 
You could do this in code.

For example, you can open an instance of the other db's workgroup so you can
supply the login details

Dim OtherWorkSpace As Workspace
Set OtherWorkSpace = DBEngine.CreateWorkspace("MyWS", "otherDBUsername",
"otherDBPassword")

Dim OtherDB As Database
Set OtherDB = OtherWorkSpace.OpenDatabase("c:\Whatever.MDB")

Next, you open the recordset that holds the table yuo wish to append to:

Dim OtherRS As Recordset
Set OtherRS = OtherDB.OpenRecordset("TableToAppendTo")

OtherRS.AddNew

And here you just do a loop picking the new values as you have from your
append query.
 
Back
Top