471,579 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,579 software developers and data experts.

Local Temporary Table - SQL

Hi there,

I'm trying to figure out a way to run an INSERT INTO sql statement that
will create a local, temporary table based on external data in a
user-level, secured Access database.

Following is sample code of the setup of my ADODB connection object
used to connect with the external database:

dim mConn as ADODB.connection
Set mConn = New ADODB.Connection

With mConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Jet OLEDB:System Database") = varWrkGrp
.ConnectionString = "data source=" & varDbPath & _
";User ID=" & varUser & _
";Password=" & varPwd
End With
With regards to the make-table sql, it is written as follows:

Dim sql As String
sql = "SELECT prodID, prodName, prodCtgy " & _
"INTO tTemp IN " & varLocalDbPath & _
"FROM tProd " & _
"WHERE prodCtgy=6"
varLocalDbPath denotes the path of the front-end .mdb file, which this
code is run on.

tProd table resides on the external, user-level, secured Access

I get an error when executing the above sql because the front-end was
opened with the default system.mdw workgroup. I guess I could remedy
this by using a short-cut to open the front-end so that the it is
opened with the same workgroup as the back-end--which I tried and the
code worked.

I'm trying to alternatively program the front-end, using the default
system.mdw file, so that its access to the back-end is encapsulated in
VBA code. The problem I'm running into is this make-table sql I'm
trying to creeate.

Is there a work around to this problem without me resorting to having
the front-end opened with the same workgroup as the back-end?


Jun 6 '06 #1
0 1117

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Beringer | last post: by
7 posts views Thread by NS Develop | last post: by
12 posts views Thread by Olumide | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.