[AccessD] Create Temp Table from Recordset

 Maybe this will help. If you give this function the name of a table it will
return the database in which the table resides. If strTable is a table that
resides in the BE "Set dbs = WhichDB(strTable)" will set a database variable
to the backend. 
 HTH
Jim Hale
 
 Function WhichDB(strTableName As String) As Database
Dim dbpath$, SourceTable$, dbTest As Database
 
On Error GoTo whichDB_ERR
Set dbTest = DBEngine(0)(0)
dbpath = Mid(dbTest(strTableName).Connect, InStr(1,
dbTest(strTableName).Connect, "=") + 1)
If dbpath = "" Then
    Set dbTest = CurrentDb()
Else
    Set dbTest = DBEngine(0).OpenDatabase(dbpath)
End If
Set WhichDB = dbTest
whichDB_EXIT:
  Exit Function
  
whichDB_ERR:
  MsgBox Err.Description
  Resume whichDB_EXIT
End Function

-----Original Message-----
From: Lavsa, Rich [mailto:Rich_Lavsa at pghcorning.com]
Sent: Monday, June 09, 2003 11:00 AM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] Create Temp Table from Recordset




You can refer to a table in another database within your queries without
having to create a permanent link to that database.
e.g. SELECT * from Orders IN "c:\datafolder\other_db.mdb"
 
Another option might be to use a Pass-Through query.  Once you have the
Pass-Through query running, you can use the Pass-Through query as your
datasource to do whatever you like such as building a make-table query...
This may or may not be a solution for you.  One of its uses is an alternate
way to "link" to a remote database table.  Look in the help file for more
information on "Pass-Through".
 
Not sure if this will solve your problem, just thought it might be something
for you to think about.  
 
Rich

-----Original Message-----
From: ACTEBS [mailto:actebs at actebs.com.au]
Sent: Monday, June 09, 2003 11: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/12b6d526/attachment-0001.html>