Copy table from other password protected db

[georgesOne]

Hi all,

in the past I used the following code to copy a table from a network based db into my local db:

DoCmd.RunSQL "SELECT tblInput.* INTO tblInput FROM tblInput IN 'Q:\Red Hat Group\ToolScheduler_be.mdb'"

Worked nice.
Now the network database is password protected. I can open the database using the password, such as

Set db = OpenDatabase(gsNetworkBE, False, False, ";PWD=gr2948du")

but I cannot run the the table make query as I do not understand how to incorporate the password.
Any help is appreciated.
Kind regards, georges

 

[PHV]

Why not simply use a linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[georgesOne]

Hi PH,
a linked table was my first approach. However, the local form to be filled - it is a sort of complex calendar form - took more than eight seconds to fill all fields via the network because of individual back and forth traffic of more than 400 controls. You could virtually see the fields filling up. The method described allows page/month changes within less than half a second, still noticable though.
The slow filling is nice to watch once or twice, but when you want to scroll through various months it is pretty embarassing. So I decided to have the feeding table on each personal machine which is updated during the form load with the SQL (here simplified) statement(there are not many active calendar changes). These few changes/updates in calendar entries are simultaneously made on the network table and the local table which is not that much of traffic and does not pose any problem.
However, recently somebody has obviously changed data in the network table (not necesarily intentionally), so we wanted to protect the direct access, and a modified SQL statement including the password would be the simplest solution, because there are lots of complex conditions involved (which is one reason for the slowliness via linked tables).
That's it and any help is still welcome.
Thanks, georges

 

[Crowley16]

the reason why linked tables are so slow is because access will bring back all the data in the link tables, then run the query/calcs locally, before giving you a result.

however if you set up a pass through query to the back end, this will send the query/calcs to be sent to the server to be worked out there, and just return the results, a much more effecient way of doing things...

--------------------
Procrastinate Now!

 

[JerryKlmns]

Crowley16

That goes for an MS-Access BE database, too?

But georgesOne, I believe that what PHV suggested, was to link the table once and make that local copy (using the linked table) whenever needed. Bind the form to the local copy not to the linked table. Would that improve performance?

 

[georgesOne]

Crowley and Jerry,

thanks for the clarifications. I had such a thing in mind but obviously I did not oversee the problem correctly.
Sounds now very reasonable to me and I will give it a try. Will have to make significant code changes though. Talk you you soon.

Kind regards, Georg