Multiple databases referenced in a single SQL query 3

[jopaumier]

I have opened two MS Access databases using VB6 and ADO. So as not to 'disturb' the two original databases, I will create a third database (also MS Access) to hold a massive table resulting from a long SQL statement that will draw from many, but not all, tables in the two databases. How do I reference these databases and the tables in each in a SQL statement to create the new table in the third database? I know I can copy tables from each of the two databases to the third and work within the single database, but I'd rather not (unless that is my only option).

Jim

PS If this post appears twice, sorry for that, but something went haywire when I submitted this the first time.

 

[johnwm]

Look up the INTO clause in SQL Books On Line. It's available as a free download from:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

No SQL or JET programmer should be without it!


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[jopaumier]

Thank you for the reminder. You had pointed out SQL BOL in an earlier question I posted - I guess two weeks vacation makes you forget about some stuff - just as it should!

 

[jopaumier]

johnwm,

I looked at the SQL Books On Line as you suggested, but need an additional push (slow learner).

I need to retrieve data from two Access databases to create a new table in a (third) Access database created at runtime. I am struggling with the syntax to reference the databases in the SQL statement to generate this new table. It's that reference to the databases that has me scratching my head.

Since we use table aliases in the FROM clause (e.g., "FROM tblPoint1 tr&quot in the current way of doing it, would it be there that we reference which database to use for the data to retrieve (e.g., "database1.tblPoint1 re" or "database2.tblUnits unt&quot? For the new table (created with the INTO clause), how do I reference the database (e.g., "INTO database3.mynewtable&quot?

Thanks,
Jim

PS We are abandoning the method of copying tables and creating a new table in an existing database (for reasons unimportant here).

 

[ElectronikBean]

Hi jopaumier,

I hope this helps... I based it on a similar bit of code to rescue a corrupt database recently. Although this bit is untested...

Public function ElectronikBean()
Dim cnMyDB1 As New ADODB.Connection
Dim cnMyDB2 As New ADODB.Connection


Set cnMyDB1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyFirstDB.mdb;"
Set cnMyDB1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MySecondDB.mdb;"

cnMyDB1.Execute _
"Insert Into tbl_BigTable In 'C:\MyThirdDB.mdb'" & _
" select * from MyOtherTable;"

cnMyDB2.Execute _
"Insert Into tbl_BigTable In 'C:\MyThirdDB.mdb'" & _
" select * from MyOtherTable;"

End Function

Hope it helps! Although you need to make sure you have ADO 2.5 referenced...

E-Bean

 

[jopaumier]

E-Bean,

Thanks for the reply. I do have ADO 2.5 referenced. Does your code append data from each database into tbl_BigTable, i.e., data from MySecondDB follows data from MyFirstDB?

In my original post I did not clearly indicate what I needed to do, but tables upon tables are joined to create the final table.

I have a working version of the SQL if all tables are in one database, but it is much too long and cumbersome to include, but here is a simplified form of it:

SELECT alias1.field1, alias1.field2, alias1.field3, alias2.field1, alias2.field2 INTO MyNewTable
FROM tblOne alias1 INNER JOIN tblTwo alias2 ON alias1.field1=alias2.field1

tblOne is from the first database and tblTwo is from a second database. MyNewTable will be in a third database

I have to join about 10 tables in order to retrieve all the necessary data. The actual FROM clause is very messy (the number of inner joins and left joins and ... yuk) and there is a WHERE clause as well.

I think I need connection information attached to tblOne, tblTwo, and MyNewTable, but I don't know just what information or the syntax, or if Access will even accept it. Seems like this would be a fairly common thing - opening and working with multiple databases at one time - just the thing for ADO rather than DAO.

Thanks,
Jim

 

[NIbarra]

Instead of copying the the tables into the third mdb you could link them and then work within that single database.

Hope this helps

 

[jopaumier]

NIbarra,

Thanks for the reply. We have actually thought about that, but how do we do it with SQL? I am at more of a loss on that syntax than with multiple connection referneces in SQL.

Again, something I did not make clear is that users will run this program on their own databases, so I cannot create the links ahead of time. Since we do not want to add tables to either of the existing databases (specified by the user) containing the original data, we decided to create a new, 'disposable' database for this new table (and other tables as well, but that is from another part of the program).

Jim

 

[CCLINT]

If both db's are JET MDBs, then use the IN keyword:
(Assumes connection is made on the first mdb)

....myTable " IN '' [;DATABASE=C:\MyData\My2ndDB.MDB;pwd="123456789]"

When using a JOIN, then this will not work and you can reference the 2nd mdb like this:

...INNER JOIN [C:\MyData\My2ndDB.MDB].myTable ON .....

Don't forget the brackets...

 

[jopaumier]

Aha! This should be just what I need. It will be painful to implement because of the length of the SQL query (in addition to the first SELECT with all its joins, I have three INSERT INTO ... SELECT's with their joins). I may be back if I run into a stumbling block.

One question, though. You say (Assumes connection is made on the first mdb). Are you saying that I really only need to connect to one database? Would it be correct to connect to create the new database (for MyNewTable) and connect to it, then reference the existing databases as you have shown?

Thanks,
Jim

 

[ElectronikBean]

I agree with CCLINT as my code did use the 'in' clause! Yet I failed to mention it

Here is a tested and debugged update!

You can use 2 connections... one for the first db and one for the third to create the table. I feel that creating and inserting the table gives you more control than select into.

This pulls code from two database, one with names, the other with places. It joins them and puts the results
in one table in a different db.

I just put a button on a form and when u press, you get a table with both the names and the places, and an ID field.

Public cnMyDB1 As New ADODB.Connection
Public cnMyDB3 As New ADODB.Connection

Private Function ElectronikBean()

cnMyDB1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyFirstDB.mdb;"
cnMyDB3.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyThirdDB.mdb;"

cnMyDB1.Open
cnMyDB3.Open


cnMyDB3.Execute _
"Create Table tbl_namesnplaces" & _
" ( [NPID] DOUBLE, [Names] TEXT(30), [Places] TEXT(30));"

cnMyDB1.Execute _
"Insert Into tbl_namesnplaces In 'C:\MyThirdDB.mdb'" & _
" SELECT [tbl_names].[field1] AS NPID, [tbl_names].[names], tbl_places.places" & _
" FROM tbl_names INNER JOIN [C:\MySecondDB].tbl_places ON [tbl_names].[field1]=tbl_places.field1;"

cnMyDB1.Close
cnMyDB3.Close


End Function

Private Sub Command1_Click()
ElectronikBean

End Sub

.. Of course you may want to add a "drop table tbl_namesnplaces" code if you run this more than once...

E-Bean

 

[jopaumier]

Great! I was just about to send another reply to CCLINT since I hit a bit of a wall when I was notified of your response. I will give it a shot. This should nail it down.

I hope I did not violate some rule of this forum by giving both CCLINT and you stars. Thanks to you both for getting me over the bump in the road - on this problem.

Jim

 

[CCLINT]

>Are you saying that I really only need to connect to one database?

Well, yes.
But if you are doing an operation on a single db though a different connection, it may take slightly longer, as a connection has to be made to that 2nd mdb as well. Then use a second connection for those single MDB operations.

You are only talking to a Race car driver(JET) and telling him which car to drive. If he is already sitting in one car, and you tell him to drive the other car, then it takes a second to get out and into the second car (well actually he'll drive the second car per remote control).

The main problem with the two connections which you will run into is the lag time between the DB writes (JET uses a lazy write cache). The second connection may not see the changes made by the first connection, until you close the first connection, or a few seconds have passed, or you refresh the cache through JRO (or with DAO bulk updates, use the dbFlushCache parameter).

You can shorten this

"IN '' [C:\MyData\My2ndDB.MDB]"

or this

"[C:\MyData\My2ndDB.MDB]."

by sticking them into variables....


> hope I did not violate some rule of this forum...
You most certainly have not!


>since I hit a bit of a wall
What wall did you hit?
(just would like to know)

 

[jopaumier]

CCLINT,

Thank you for the additional information.

I did set up strings with the necessary information to use in building my SQL query. No way was I going to retype the stuff.

The wall I ran into was how to address the table in the third database (the new one) if only one connection was established. I was not sure if SELECT ... INTO ... IN ... FROM would be the correct syntax so I was going to ask you (there was an error with what I had completed - punctuation problem somewhere so the SQL did not execute). I did a SELECT ... INTO ... FROM ... IN to copy a table from one mdb to another. I did not know if SELECT ... INTO ... IN ... FROM would work as well. From E-Bean's reply, it looks like he likes INSERT INTO ... IN ... SELECT ...FROM ...

One other quick question. Is it just experience with SQL and databases that you know the syntax, or is there a really good book on all this? Johnwm suggested SQL Server Books On Line, but it is written in that style that sometimes makes it hard (for me) to understand the syntax, then ther is the question of punctuation - single, double quotes and all - that drives me crazy. It is tough being an old newbie and the only one in the office working with VB.

Jim

 

[jopaumier]

To all who responded --

Thank you. I've got the code working now and the new table is populated correctly. I used SELECT... INTO to create and populate the table initially, then INSERT INTO to keep adding more data, since I have 25-30 fields.

Hey E-Bean, should you see this, does your method of creating the table

cnMyDB3.Execute _
"Create Table tbl_namesnplaces" & _
" ( [NPID] DOUBLE, [Names] TEXT(30), [Places] TEXT(30));"

set the Required property in an Access database to Yes or No (I have not had a chance to test it)? For another program I had to use DAO to create tables because ADO insisted on making all fields Required, which was not necessarily going to be true. I posted a question on Tek-Tips and got several replies, but none seemed to work - maybe I was not persistent enough. Anyway, can you set that property in the code you suggested?

Jim

 

[ElectronikBean]

Jim,

I have just had a look at the table it creates, and it defaults to required = "NO".


Intresting question though - I will have a look into that when I get the chance. I am going to get some sleep now... its getting late here in Devon,UK. I may look into it next week sometime...

Nik

 

[CCLINT]

>because ADO insisted on making all fields Required

If this is happening, then it isn't ADO...It is the JET provider that handles the DDL operations.
You may want to have a look at ADOX for ease of use when creating/changeíng tables, fields, ect, or even go back to DAO for ease of use with DDL operations.
Absolutly nothing wrong or negative using DAO (or ADOX) along with ADO for these DDL operations on a JET database.

Please refer to the help files mentioned in thread709-586400 for the exact syntax and methods for JET DDL operations through DAO or ADO.

 

[jopaumier]

Hmm, now I have some more investigating to do. At least I can get the job done with DAO for now.

Hope you got a good night's sleep E-Bean

Jim