Hi;



I want to create an SQL recordset from two Access(97) tables which reside 

in SEPARATE Access MDB databases ... any ideas?



(PS. Msg to moderator - I posted this msg to Access forum in error -it 

should be here)



Tia ///Edward

www.Sumatrix.com

Insert a "linked table" in one of the MDB databases to the actual table in

the other MDB.



-- Z







----- Original Message -----

From: "Ed Rivis" <edrivis@b...>

To: "Access ASP" <access_asp@p...>

Sent: Monday, December 03, 2001 2:15 PM

Subject: [access_asp] Create SQL recordset from two tables which reside in

separate Access databases?





> Hi;

>

> I want to create an SQL recordset from two Access(97) tables which reside

> in SEPARATE Access MDB databases ... any ideas?

>

> (PS. Msg to moderator - I posted this msg to Access forum in error -it

> should be here)

>

> Tia ///Edward

> www.Sumatrix.com

>

>

>




>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Ed Rivis" <edrivis@b...>

Subject: [access_asp] Create SQL recordset from two tables which reside in

separate Access databases?





: I want to create an SQL recordset from two Access(97) tables which reside

: in SEPARATE Access MDB databases ... any ideas?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



a) Import one table into the other database

b) Linked Tables?

c) Create two recordets, then use .GetString to get a string variable, and

concatenate the two variables and use Split() to get an array.



...none of these options is really palatable. You haven't said yet how you

want to create this "recordset" - a JOIN, a UNION etc - that would probably

help in determining what's the easiest way to create this "recordset"



Cheers

Ken

Thx for your reply. I tried the link table but it stores the path to the 

linked table which works fine when I test locally but when I upload to the 

server it messes things up. I could simulatre the server path on my local 

machine but then that wouldn;t help my client when they want to download 

and review data.



///Ed



> Insert a "linked table" in one of the MDB databases to the actual table 

in

> the other MDB.

> 

> -- Z

> 

> 

> 

> ----- Original Message -----

> From: "Ed Rivis" <edrivis@b...>

> To: "Access ASP" <access_asp@p...>

> Sent: Monday, December 03, 2001 2:15 PM

> Subject: [access_asp] Create SQL recordset from two tables which reside 

in

> separate Access databases?

> 

> 

> > Hi;

> >

> > I want to create an SQL recordset from two Access(97) tables which 

reside

> > in SEPARATE Access MDB databases ... any ideas?

> >

> > (PS. Msg to moderator - I posted this msg to Access forum in error -it

> > should be here)

> >

> > Tia ///Edward

> > www.Sumatrix.com

> >

> >

> >




> >

> 

Hi Ken;



Thx for verbose reply!  Yes, I just want to SQL inner join on a primary 

key in a table in database1 with a foreign key in a table in database2. I 

see in Access you can manually enter SQL in query and specify an 

IN "path\database" but this fails when I move the whole thing from local 

to server.  (I normally use VFP so I'm an Access newbie in this area.)



Thanks for your help.

///Ed



> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Ed Rivis" <edrivis@b...>

> Subject: [access_asp] Create SQL recordset from two tables which reside 

in

> separate Access databases?

> 

> 

> : I want to create an SQL recordset from two Access(97) tables which 

reside

> : in SEPARATE Access MDB databases ... any ideas?

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> 

> a) Import one table into the other database

> b) Linked Tables?

> c) Create two recordets, then use .GetString to get a string variable, 

and

> concatenate the two variables and use Split() to get an array.

> 

> ...none of these options is really palatable. You haven't said yet how 

you

> want to create this "recordset" - a JOIN, a UNION etc - that would 

probably

> help in determining what's the easiest way to create this "recordset"

> 

> Cheers

> Ken

> 

Is there any possibilty that you could import the data from database into

the other? (this can be done programmatically, but I don't know about your

business conditions...)



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Ed Rivis" <edrivis@b...>

Subject: [access_asp] Re: Create SQL recordset from two tables which reside

in separate Access databases?





: Hi Ken;

:

: Thx for verbose reply!  Yes, I just want to SQL inner join on a primary

: key in a table in database1 with a foreign key in a table in database2. I

: see in Access you can manually enter SQL in query and specify an

: IN "path\database" but this fails when I move the whole thing from local

: to server.  (I normally use VFP so I'm an Access newbie in this area.)

:

: Thanks for your help.

: ///Ed



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi Ken;



Could do but then the reason I split the database was to keep lookup 

tables and less frequently changing data separate from data created by 

visitors to the web site....  I've been pouring myself over this and I see 

that what I should be able to do is Link the Lookup tables of database 'x' 

into database 'y' using a DSN string (rather than Linking direct and it 

storing paths which become hard coded and then incorrect when upload to 

web server) ...but Access doesn't appear to like this....



Getting More and more Frustrated!  <g>



Thx for your thoughts

///Ed



> Is there any possibilty that you could import the data from database into

> the other? (this can be done programmatically, but I don't know about 

your

> business conditions...)

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Ed Rivis" <edrivis@b...>

> Subject: [access_asp] Re: Create SQL recordset from two tables which 

reside

> in separate Access databases?

> 

> 

> : Hi Ken;

> :

> : Thx for verbose reply!  Yes, I just want to SQL inner join on a primary

> : key in a table in database1 with a foreign key in a table in 

database2. I

> : see in Access you can manually enter SQL in query and specify an

> : IN "path\database" but this fails when I move the whole thing from 

local

> : to server.  (I normally use VFP so I'm an Access newbie in this area.)

> :

> : Thanks for your help.

> : ///Ed

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> 

Have you thought about caching the "look-up" data in application arrays? or

similar?



To be honest, I'm not really sure what you're gaining by splitting some of

these tables into a new database file, and then linking one DB file to the

other...



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Ed Rivis" <edrivis@b...>

Subject: [access_asp] Re: Create SQL recordset from two tables which reside

in separate Access databases?





: Hi Ken;

:

: Could do but then the reason I split the database was to keep lookup

: tables and less frequently changing data separate from data created by

: visitors to the web site....  I've been pouring myself over this and I see

: that what I should be able to do is Link the Lookup tables of database 'x'

: into database 'y' using a DSN string (rather than Linking direct and it

: storing paths which become hard coded and then incorrect when upload to

: web server) ...but Access doesn't appear to like this....

:

: Getting More and more Frustrated!  <g>



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hi Ken;



Thx again.  I split them cos the lookup set of tables will be large but 

infrequently changing (maybe only a few times a year), whereas the user 

set of tables will be accessed and updated on a daily basis by visitors to 

the web site (hopefully! ;-)



So with the split design my clients will be able to download just 

the 'user database' whenever they want for offline analysis, and that the 

web site need never go offline when the lookup data is edited... this can 

just be done offline and uploaded as and when necessary.



I've actually got a solution now, using your suggestion of multiple 

recordsets and then looping through them in code etc.



**Thanks for all your help on this - *most appreciated**



Best regards,

/// Ed. 

Sumatrix.com



> Have you thought about caching the "look-up" data in application arrays 

or similar? To be honest, I'm not really sure what you're gaining by 

splitting some of these tables into a new database file, and then linking 

one DB file to the other...

> 

> Cheers

> Ken

Um...I didn't suggest multiple recordsets & loops. That's the type of thing

I not likely to suggest :-)



I was thinking more along the lines of:

http://www.aspfriends.com/learn/rsfast.asp

where you cache the non-changing data in Application level arrays...



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Ed Rivis" <edrivis@b...>

Subject: [access_asp] Re: Create SQL recordset from two tables which reside

in separate Access databases?





: Hi Ken;

:

: Thx again.  I split them cos the lookup set of tables will be large but

: infrequently changing (maybe only a few times a year), whereas the user

: set of tables will be accessed and updated on a daily basis by visitors to

: the web site (hopefully! ;-)

:

: So with the split design my clients will be able to download just

: the 'user database' whenever they want for offline analysis, and that the

: web site need never go offline when the lookup data is edited... this can

: just be done offline and uploaded as and when necessary.

:

: I've actually got a solution now, using your suggestion of multiple

: recordsets and then looping through them in code etc.

:

: **Thanks for all your help on this - *most appreciated**

:

: Best regards,

: /// Ed.

: Sumatrix.com

:

: > Have you thought about caching the "look-up" data in application arrays

: or similar? To be honest, I'm not really sure what you're gaining by

: splitting some of these tables into a new database file, and then linking

: one DB file to the other...

: >

: > Cheers

: > Ken

> Um...I didn't suggest multiple recordsets & loops. That's the type of 

thing

> I not likely to suggest :-)



OOOPS!

 

> I was thinking more along the lines of:

> http://www.aspfriends.com/learn/rsfast.asp

> where you cache the non-changing data in Application level arrays...



Thanks for this great resource - lots there - maybe next time I quote 

that "I do what Ken does" I'll get it right!  ;-)



Thank you.

Ed.



> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> From: "Ed Rivis" <edrivis@b...>

> Subject: [access_asp] Re: Create SQL recordset from two tables which 

reside

> in separate Access databases?

> 

> 

> : Hi Ken;

> :

> : Thx again.  I split them cos the lookup set of tables will be large but

> : infrequently changing (maybe only a few times a year), whereas the user

> : set of tables will be accessed and updated on a daily basis by 

visitors to

> : the web site (hopefully! ;-)

> :

> : So with the split design my clients will be able to download just

> : the 'user database' whenever they want for offline analysis, and that 

the

> : web site need never go offline when the lookup data is edited... this 

can

> : just be done offline and uploaded as and when necessary.

> :

> : I've actually got a solution now, using your suggestion of multiple

> : recordsets and then looping through them in code etc.

> :

> : **Thanks for all your help on this - *most appreciated**

> :

> : Best regards,

> : /// Ed.

> : Sumatrix.com

> :

> : > Have you thought about caching the "look-up" data in application 

arrays

> : or similar? To be honest, I'm not really sure what you're gaining by

> : splitting some of these tables into a new database file, and then 

linking

> : one DB file to the other...

> : >

> : > Cheers

> : > Ken

> 

>