[AccessD] Create Temp Table from Recordset

Vlad,
 
When using the QBE grid to create the SQL statement, go to the properties of
the query and change the source database to point to the remote DB.  Then
when you go to add tables, the list of tables in the remote database should
show up in the list.  When you do this your SQL statement should look
something like the following:
 
SELECT tblRemote.PlanRefNum, tblRemote.AllianceFundName, tblRemote.Cusip
INTO tblTMP
FROM tblRemote IN 'f:\access\RemoteDB.mdb';
 
tblRemote is the table you are pulling data from in f:\access\RemoteDB.mdb,
tblTMP is the new table being created.
 
Rusty

-----Original Message-----
From: ACTEBS [mailto:actebs at actebs.com.au]
Sent: Monday, June 09, 2003 10:13 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Create Temp Table from Recordset


John/Rusty,
 
I don't think I explained myself very well. I have a remote DB not linked to
my FE. I could create a stored query, but it won't run because the tables
are not linked. What I need to do is create the table from the recordset
that I've created so I can work on the data locally and bind it to a report.
 
Any other suggestions?
 
Vlad

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Colby
Sent: Tuesday, 10 June 2003 12:59 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Create Temp Table from Recordset


do it with the SQL statement directly
 
Create a make table query using the query wizard and then look at the syntax
in SQL view.
 

John W. Colby
www.colbyconsulting.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of ACTEBS
Sent: Monday, June 09, 2003 10:48 AM
To: access group
Subject: [AccessD] Create Temp Table from Recordset


Guys,
 
Is there a method of creating a temp table in your DB from a recordset? eg:
 
dim strSQL as string
dim db as dao.database
dim rst as dao.recordset
 
strSQL = "SELECT someTable etc etc etc"
 
set db = opendatabase("some remote DB")
set rst =db.openrecordset(strsql)
 
with rst
    make a new table in my db from the recordset
end with
 
I've never had to do this and have wasted heaps of time on this. Any help
will be much appreciated...
 
Thanks
Vlad
 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030609/0a1b22a6/attachment-0001.html>