recordset from multiple backend .mdb

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a query to combine query results from 5 .mdb into one table
in Excel.

Results will have dates in col 1, and data in each of next 5 columns,
ordered by dates in col 1. (dates will be generated using dateadd monthly
from start to end date, so some columns may have nulls.)

I am versed in simple SQL but am stuck on 2 complexities:

1) I'm returning the same field name for 5 col (different query parameters)
2) I'm drawing from 5 databases

additionally there could end up being 10-15 columns in the future, and teh
whole thing is dynamic so needs to be in VBA code

I know how to structure each query, but what is the best way to combine them?

Is there a simple way to do this or do I need to construct a new table and
update from the others...so far I have only come up with very complicated
answers that I'd rather not code...

Thanks
 
=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=
1) I'm returning the same field name for 5 col (different query
parameters)
Click to expand...

for obvious reasons you can't: you'll have to alias them
2) I'm drawing from 5 databases
Click to expand...

the help files say you can only use one IN clause in a query, but I am
pretty sure I've seen queries with more. In any case, the first thing I'd
try would be

SELECT Data1.SomeDate,
Data2.SomeField AS SomeStuff2,
Data3.SomeField AS SomeStuff3,
Data4.SomeField AS SomeStuff,
Data5.SomeField AS SomeStuff

FROM BaseTable IN [Excel: "z:\data\xl\basedata.xls!Sheet1"]
AS Data1
LEFT JOIN SomeSheet IN [Excel: "s:\shared\janice.xls!Sheet2"]
AS Data2
LEFT JOIN BlueSheet IN [Excel: "s:\shared\alice.xls!Sheet2"]
AS Data3
LEFT JOIN DataSheet IN [Excel: "s:\shared\jenny.xls!Sheet2"]
AS Data4
LEFT JOIN FredSheet IN [Excel: "s:\shared\bernie.xls!Sheet2"]
AS Data5
ON Data1.SomeDate = Data5.SomeDate
ON Data1.SomeDate = Data4.SomeDate
ON Data1.SomeDate = Data3.SomeDate
ON Data1.SomeDate = Data2.SomeDate

ORDER BY Data1.SomeDate ASC

No I haven't tested this!! Whatever the correct version is, it should be
fairly easy to build in VBA as long as you can program the names of the
sheets and workbooks etc.

Hope it helps


Tim F
 
Tim -
thanks for the quick reply, I like the multipl INs and with the aliasing I'm
half way there, but let me clarify:

The data is comming from ACCESS backend .mdb's

I'm used to only one open connection and sending the SQL as:
set rst = cnn.Execute mySQLstmt

does this allow for multiple databases to be queried by one SQL statement?
and What Syntax would i use for multiple Access connections?

mysqlstmt = "...IN [Access: "s:\shared\bernie.mdb!mytable" ] As DATA1 ..."
???
set rst = docmd.runSQl mysqlstmt ???

IN ______________________ As DATA1

Thanks again, and again

Tim Ferguson said:
=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=
1) I'm returning the same field name for 5 col (different query
parameters)
Click to expand...

for obvious reasons you can't: you'll have to alias them
2) I'm drawing from 5 databases
Click to expand...

the help files say you can only use one IN clause in a query, but I am
pretty sure I've seen queries with more. In any case, the first thing I'd
try would be

SELECT Data1.SomeDate,
Data2.SomeField AS SomeStuff2,
Data3.SomeField AS SomeStuff3,
Data4.SomeField AS SomeStuff,
Data5.SomeField AS SomeStuff

FROM BaseTable IN [Excel: "z:\data\xl\basedata.xls!Sheet1"]
AS Data1
LEFT JOIN SomeSheet IN [Excel: "s:\shared\janice.xls!Sheet2"]
AS Data2
LEFT JOIN BlueSheet IN [Excel: "s:\shared\alice.xls!Sheet2"]
AS Data3
LEFT JOIN DataSheet IN [Excel: "s:\shared\jenny.xls!Sheet2"]
AS Data4
LEFT JOIN FredSheet IN [Excel: "s:\shared\bernie.xls!Sheet2"]
AS Data5
ON Data1.SomeDate = Data5.SomeDate
ON Data1.SomeDate = Data4.SomeDate
ON Data1.SomeDate = Data3.SomeDate
ON Data1.SomeDate = Data2.SomeDate

ORDER BY Data1.SomeDate ASC

No I haven't tested this!! Whatever the correct version is, it should be
fairly easy to build in VBA as long as you can program the names of the
sheets and workbooks etc.

Hope it helps


Tim F
Click to expand...
 
=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=
I'm used to only one open connection and sending the SQL as:
set rst = cnn.Execute mySQLstmt

does this allow for multiple databases to be queried by one SQL
statement? and What Syntax would i use for multiple Access
connections?

mysqlstmt = "...IN [Access: "s:\shared\bernie.mdb!mytable" ] As DATA1
..." ???
Click to expand...


I think it's MyTable IN [;DATABASE="s:\shared\bernie.mdb"] AS etc
set rst = docmd.runSQl mysqlstmt ???

IN ______________________ As DATA1
Click to expand...

You may be better off looking in a ADO group rather than a DAO one. I am
by no means an ADO expert, but here goes. :-) I did some looking round
the MSDN and came across this:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";' +
'User ID=Admin;Password=;Extended properties=Excel 5.0'
)...xactions


so you might do some experimenting around there, using the OpenDataSource
command. It's here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_oa-oz_2be1.asp

Since you are looking at Jet databases, would it not be easier to set up
table links to the source data, and then construct the query using the
local tabledefs? I don't know exactly how to do this in ADOX, but it's
not hard in DAO.

Greetings from the edge of my competence!
All the best


Tim F
 
TIM -

THANK YOU!

I have determined:
1) i am over my head.........and loving it thanks to your help
2) There was no easy answer
3) As mainly an Excel guy this DB thing takes time..

With your guidance i am proceeding as follows:

using ADO assistance from 16-Oct-2002 by Andy Wiggins - Byg Software Ltd
- thanks Andy for downloadabe .xls with code for mdb table & field
manipulation

All my .mdb are in one folder

I have established one more .mdb that on the fly creates a table for each of
my other mdb tables with a link - table name is based on the found mdb

query is against this new mdb using the table names

not sure how "pretty" or fast this will be but it does allow te "full plug &
play" that I was looking for with the addition and deletion of available
data.mdb being as simple and moving into/out of the folder.

Thanks again for saving me the days it would have take to give up and
redesign in a fashion I might be able to code..

Have a great weekend

Tim Ferguson said:
=?Utf-8?B?VmFjYXRpb24ncyBPdmVy?=
I'm used to only one open connection and sending the SQL as:
set rst = cnn.Execute mySQLstmt

does this allow for multiple databases to be queried by one SQL
statement? and What Syntax would i use for multiple Access
connections?

mysqlstmt = "...IN [Access: "s:\shared\bernie.mdb!mytable" ] As DATA1
..." ???
Click to expand...


I think it's MyTable IN [;DATABASE="s:\shared\bernie.mdb"] AS etc
set rst = docmd.runSQl mysqlstmt ???

IN ______________________ As DATA1
Click to expand...

You may be better off looking in a ADO group rather than a DAO one. I am
by no means an ADO expert, but here goes. :-) I did some looking round
the MSDN and came across this:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";' +
'User ID=Admin;Password=;Extended properties=Excel 5.0'
)...xactions


so you might do some experimenting around there, using the OpenDataSource
command. It's here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/tsqlref/ts_oa-oz_2be1.asp

Since you are looking at Jet databases, would it not be easier to set up
table links to the source data, and then construct the query using the
local tabledefs? I don't know exactly how to do this in ADOX, but it's
not hard in DAO.

Greetings from the edge of my competence!
All the best


Tim F
Click to expand...
 
Back
Top