473,372 Members | 1,010 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 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 1179

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Corrine | last post by:
Hi, I am creating a global temporary table that is session-specific. I insert a BLOB into this table, and then select the BLOB from this table into a ResultSet. The ResultSet sees this BLOB...
by: gonzal | last post by:
Hi Dose any body know why a temporary table gets deleted after querying it the first time (using SELECT INTO)? When I run the code bellow I'm getting an error message when open the temp table...
by: Beringer | last post by:
I have created a local temporary table (#Temp) and placed data in it. When I do: SELECT * #Temp The result is a set of rows with no columns. The SELECT statement is in the same procedure as...
by: NS Develop | last post by:
What's wrong with the following: void SomeClass::someMethod(void) { A *ptrA = NULL; ptrA = &A(); .... }
by: Olumide | last post by:
I'm studying Nigel Chapman's Late Night Guide to C++ which I think is an absolutely fantastic book; however on page 175 (topic: operator overlaoding), there the following code snippet: inline...
by: ezra epstein | last post by:
I found a post about something similar in an older release: http://archives.postgresql.org/pgsql-bugs/2002-08/msg00151.php Here's the issue. Inside a function I'm calling CREATE LOCAL TEMPORARY...
by: pinballjim | last post by:
Hello everyone, I'm looking for a simple way to create a local copy of a linked table. I've got a database that links about 10 tables from other databases. This works fine on my machine, but I...
by: Larry | last post by:
Hi, I have unbelievable problems just to save a record! I make an input to a record in a subform, which has a temporary table as its recordsource. When I am done, and want to save the...
by: George2 | last post by:
Hello everyone, 1. Returning non-const reference to function local object is not correct. But is it correct to return const reference to function local object? 2. If in (1), it is correct...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.