Move Query data from one DB to another?

[briglass]

Hello all!

I have two databases and both are exact copies. They both have a crosstab query. One of the queries has data in it.

Is there a way, from within Visual Basic 6.0, to quickly move the data?

Or, is there a way, from within Visual Basic 6.0, to simply move the entire query, data and all, from one DB to the other?

Thanks,
Brian

 

[pdx_man]

(Not to be too elementary, but I don't know your level of expertise in databases. )

Well, for starters, queries don't contain data ... tables do. Queries just diplay the data in the tables in the specifics defined by the SQL statement. If, in fact, the queries are in both places EXACTLY the same, then you have other issues with the underlying tables. OR the queries are not the same.

To copy the query from one DB to the other:
What you need to do, is copy the query's syntax (SQL statement) into the other DB. I am assuming that you have created a connection object in VB6 to your databases. You also need a QueryDef (let's call it QDef_Mast & QDef_Slv) object pointing to the crosstab query. Use QDef_Slv.SQL = QDef_Mast.SQL statement to retreive the SQL from the main DB to the other DB.

Hopefully, this will get you pointed in the right direction.

 

[briglass]

Thank you for your reply! Also, I do appreciate *simple* explanations, if you get my drift.

Ok, basically I have a VB program designed to merge data from one slave DB to another master DB (both with the same structure, and they both contain data). This is so that our clients (who use the database with our OTHER VB program to collect data into the DB) can use this new VB program to merge all their data into one DB. Make sense?

My merging VB program successfully merges the data in the DB's tables. However, I realized that there is a query that has lots of "entries." I need to be able to bring these entries to the final (master) DB.

Both query's have the same SQL statement.

As you say, if the query displays data based on the SQL statement, and I have merged in all the data, then I should be able to "run" the query some how and bring the data in. How do I do this?

Thank you very much for your help! This is not for a school project.

Thank you,
Brian

 

[pdx_man]

I'm not sure I understand this statement.

However, I realized that there is a query that has lots of "entries." I need to be able to bring these entries to the final (master) DB.

Can you explain the query that has lots of entries?

 

[briglass]

No problem.

When I click on the crosstab query in a clean database (a database that hasn't been used to collect data yet) there is nothing in the query (except of course the SQL code). When I click on a crostab query in a database used to collect data, there are columns and rows of *numbers* ( to use a word other than *entries* which I was using as a term other than *data* ).

Is this more clear? I need to get these *entries* into a clean database using Visual Basic.

??

Thanks!

-Brian

 

[pdx_man]

Sorry Brian, I must be having a bad week, 'cause I'm not gettin' it.

When I click on the crosstab query in a clean database (a database that hasn't been used to collect data yet) there is nothing in the query (except of course the SQL code).

You have a database with no data in it and you are running a query and wondering why no results are being returned?
Crosstab queries generate their column names from data. So ... you need to get the data from one database to the other? Is this the root of what you are trying to do?

 

[briglass]

I have lots of data in the database. I moved all that over from within Visual Basic.

But, nothing comes up in the crosstab query once I move it. The old database has *data* in the tables AND *entries* in the query. The new database has *data* in the tables AND NO *entries* in the query?

Does that make sense?

Thanks,
Brian

 

[briglass]

When I said this:

"When I click on the crosstab query in a clean database (a database that hasn't been used to collect data yet) there is nothing in the query (except of course the SQL code). "

I was just trying to explain what I meant by "entries."

Thanks,
Brian

 

[pdx_man]

There must be some disconnect between the two databases. Either the SQL is different, or the tables are. Hmmm Perhaps the relationships might be different ... Do you have them defined in both places? Otherwise, zip the dbs and email them to me.

 

[briglass]

They are copies of each other. I'm just wondering: Once I have moved the data into the database with Visual Basic, how can I "run" the query or get the query to "run" it's SQL code.

I can email you the databases if you think that would help. One with data AND "entries" in the query, and one with data AND NO "entries" in the query.

Thanks,
Brian

 

[pdx_man]

You can create a Recordset object pointing to the query in the second database. Use the RecordCount method to see if there is any data being returned by the query. Go ahead and zip the files and email them to me. I think there must be some other disconnect ...

 

[briglass]

OK, I will email you the files. When you say "disconnect" do you mean discrepancy? The files shouldn't really be connected.

Also, I should warn you that I am sending you 4 files. Our data is stored in a pair of databases: dis4.mdb (which is linked to) dis4data.mdb.

I will explain more in a readme.

Thanks!
Brian

 

[briglass]

Actually, I'm not sure how I'm supposed to email you...

I can't find your email addy, and also when I click to email you from your profile I don't see a way to attach a file.

?

Thanks,
Brian