RunSQL to Append local table

[vbc22]

Hi there, does anyone know how I get a docmd.runSql command to append a local table based on external data on a back-end database that is password protected?

Here's my statement:

DoCmd.RunSQL "INSERT INTO tTempTbl( fname, lname, strEmail)" & _
             "SELECT fname, lname, strEmail FROM tPeople"

tTempTbl is the local table I want to append records into.
tPeople is a linked table of the password protected back-end.

Back-end is an Access database.

Because the records I am wanting append to the local table is from a password protected back-end, I'm getting an error.

Is the command statement valid only for non-protected, external data sources?

I would appreciate any advice someone can offer.

Thanks.

 

[vbc22]

I am just wondering...would I have to do it in 2 parts in order to bypass using the DoCmd.RunSQL command?

1. create ADODB recordset with password string to retrieve data
2. use VBA code to populate recordset into a local table

The reason for the local table is because I want to use it as a source to feed an automated Excel Report that the user can generate from. It would be unfeasible to keep the connection running to create the Excel Report.

When the password to the back-end was not set, the speed performance was very good--that's why I chose the DoCmd.RunSQL command approach to populate to a local table.

Any suggestions?

Regards.

 

[JerryKlmns]

You could use the IN clause

Insert Into tTempTbl( fname, lname, strEmail) IN "C:\extradb.mdb"
SELECT fname, lname, strEmail FROM tPeople

But as the help file points, for improved performance and ease of use, use a linked table instead of IN.

I would link

 

[vbc22]

Problem is still with the password to access that external data source though. Where is the syntax to apply the password?

 

[PHV]

Have you tried to recreate the linked tPeople table with password remembered ?

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

 

[vbc22]

Thanks for the advice...I forgot about passwords remembered with linking tables, but I only remember being able to remember passwords (checkbox) when using ODBC to link tables.

If linking an mdb to another mdb, there's no password checkbox to keep the password.

still searching...

 

[AccessGuruCarl]

vbc22,

If you create an Append query and run it, does it work?
View the SQL View - Copy that code into your vba.

The frontend should remember the password to the back-end db if the tables are linked.

Good Luck,
Carl

AccessGuruCarl
Programmers helping programmers
you can't find a better site.