<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2800.1126" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2> <SPAN
class=036383319-09062003>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.
</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=036383319-09062003> HTH</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=036383319-09062003>Jim Hale</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2> Function WhichDB(strTableName
As String) As Database<BR>Dim dbpath$, SourceTable$, dbTest As
Database</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2>On Error GoTo whichDB_ERR<BR>Set
dbTest = DBEngine(0)(0)<BR>dbpath = Mid(dbTest(strTableName).Connect, InStr(1,
dbTest(strTableName).Connect, "=") + 1)<BR>If dbpath = ""
Then<BR> Set dbTest =
CurrentDb()<BR>Else<BR> Set dbTest =
DBEngine(0).OpenDatabase(dbpath)<BR>End If<BR>Set WhichDB =
dbTest<BR>whichDB_EXIT:<BR> Exit Function<BR>
<BR>whichDB_ERR:<BR> MsgBox Err.Description<BR> Resume
whichDB_EXIT<BR>End Function</FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Lavsa, Rich
[mailto:Rich_Lavsa@pghcorning.com]<BR><B>Sent:</B> Monday, June 09, 2003 11:00
AM<BR><B>To:</B> 'accessd@databaseadvisors.com'<BR><B>Subject:</B> RE:
[AccessD] Create Temp Table from Recordset<BR><BR></FONT></DIV>
<DIV><SPAN class=226004115-09062003>
<DIV><SPAN class=226004115-09062003><SPAN class=226004115-09062003><FONT
face=Arial color=#0000ff size=2>
<DIV><SPAN class=226004115-09062003></SPAN><FONT face=Arial color=#0000ff
size=2><SPAN class=226004115-09062003>You can refer to a table in another
database within your queries without having to create a permanent link to that
database.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=226004115-09062003>e.g.
SELECT * from Orders IN
"c:\datafolder\other_db.mdb"</SPAN></FONT></DIV></FONT></SPAN></SPAN></DIV>
<DIV><SPAN class=226004115-09062003><SPAN class=226004115-09062003><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></SPAN> </DIV>
<DIV><SPAN class=226004115-09062003><SPAN class=226004115-09062003><FONT
face=Arial color=#0000ff size=2>Another option might be to use a Pass-Through
query. </FONT></SPAN></SPAN></SPAN><SPAN class=226004115-09062003><FONT
face=Arial color=#0000ff size=2>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".</FONT></SPAN></DIV></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=226004115-09062003></SPAN></FONT> </DIV>
<DIV><SPAN class=226004115-09062003><FONT face=Arial color=#0000ff size=2>Not
sure if this will solve your problem, just thought it might be something for
you to think about. </FONT></SPAN></DIV>
<DIV><SPAN class=226004115-09062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=226004115-09062003><FONT face=Arial color=#0000ff
size=2>Rich</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> ACTEBS
[mailto:actebs@actebs.com.au]<BR><B>Sent:</B> Monday, June 09, 2003 11:13
AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> RE:
[AccessD] Create Temp Table from Recordset<BR><BR></FONT></DIV>
<DIV><SPAN class=412040915-09062003><FONT face=Arial color=#0000ff
size=2>John/Rusty,</FONT></SPAN></DIV>
<DIV><SPAN class=412040915-09062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=412040915-09062003><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=412040915-09062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=412040915-09062003><FONT face=Arial color=#0000ff
size=2>Any other suggestions?</FONT></SPAN></DIV>
<DIV><SPAN class=412040915-09062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=412040915-09062003><FONT face=Arial color=#0000ff
size=2>Vlad</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com] <B>On Behalf Of </B>John
Colby<BR><B>Sent:</B> Tuesday, 10 June 2003 12:59 AM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> RE: [AccessD] Create Temp
Table from Recordset<BR><BR></FONT></DIV>
<DIV><SPAN class=279525714-09062003><FONT face=Arial color=#0000ff
size=2>do it with the SQL statement directly</FONT></SPAN></DIV>
<DIV><SPAN class=279525714-09062003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=279525714-09062003><FONT face=Arial color=#0000ff
size=2>Create a make table query using the query wizard and then look at
the syntax in SQL view.</FONT></SPAN></DIV>
<DIV> </DIV>
<P><FONT size=2>John W. Colby<BR>www.colbyconsulting.com</FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of
</B>ACTEBS<BR><B>Sent:</B> Monday, June 09, 2003 10:48 AM<BR><B>To:</B>
access group<BR><B>Subject:</B> [AccessD] Create Temp Table from
Recordset<BR><BR></FONT></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2>Guys,</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>Is there a
method of creating a temp table in your DB from a recordset?
eg:</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>dim strSQL
as string</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>dim db as
dao.database</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>dim rst as
dao.recordset</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>strSQL =
"SELECT someTable etc etc etc"</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>set db =
opendatabase("some remote DB")</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>set rst
=db.openrecordset(strsql)</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>with
rst</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003> <FONT face=Arial
size=2>make a new table in my db from the recordset</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>end
with</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial size=2>I've never
had to do this and have wasted heaps of time on this. Any help will be
much appreciated...</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2>Thanks</FONT></SPAN></DIV>
<DIV><SPAN class=876254314-09062003><FONT face=Arial
size=2>Vlad</FONT></SPAN></DIV>
<DIV> </DIV></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BLOCKQUOTE></BODY></HTML>