Compare two tables
Compare two tables
Compare two tables
Compare two tables
Compare two tables
Compare two tables Compare two tables Compare two tables Compare two tables Compare two tables Compare two tables Compare two tables Compare two tables
Compare two tables Compare two tables
Compare two tables
Go Back  Xtreme Visual Basic Talk > > > Compare two tables


Reply
 
Thread Tools Display Modes
  #1  
Old 05-12-2004, 02:07 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default Compare two tables


I have a version of a Db stored locally on my c drive for remote use. There is a master copy on the network. What I want to do is make a comparison between the tables in the two DBs and decide if there are updates needed to the master (ie. I have made changes while in remote mode). Anyone have a good way to do this?
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #2  
Old 05-12-2004, 06:55 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What database engine are you using? Depending on what you are using, there are many different ways to do this:

If the two tables have the same structure, you could do union query, and then do a query that returns unique records only (DISTINCT or DISTINCT ROW).
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 05-13-2004, 08:12 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by MKoslof
What database engine are you using? Depending on what you are using, there are many different ways to do this:

If the two tables have the same structure, you could do union query, and then do a query that returns unique records only (DISTINCT or DISTINCT ROW).

I am using Microsoft.Jet.OLEDB.4.0 with an Access DB. The tables will be identical in structure as one is just a copy of the original. Only the data might be a little different.

Actually now that I have your ear (or monitor) on this one, is this the best way to be doing this? As it is now what I do is on Friday (or what ever day I am leaving the office for a few days) I do a copy of the DB (re my post on file transfer API) to my HDD on my lap top. Because I am always on call 24/7/365 I need to have access to my DB always . So if I get a service call while in remote mode, the path to the DB gets changed (re my post on detecting network connection) to the local version of the DB and stores the data there. NOW...When I get back to the office and start up my program it will see that the DB has changed (I make a blank txt file called update.txt for the program to look for) and begins transferring only the records that are different FROM the local copy TO the Network copy. The next time I leave the office I overwrite the old local copy with a new backup.


Also.... i was just looking at the options for the UNION & DISTINCT statements. If my 2 DBs are identical )one is a copy of the other) how to I specify in my SQL statement which table I am talking about?

SQL = "SELECT * FROM tableA UNION SELECT * FROM TableA"

as far as the DISTINCT ROW SQL call I'm unfamilliar with it.
__________________
Win Win Situation: Swapping my salary with my golf score

Last edited by VisuallyImpared; 05-13-2004 at 08:53 AM.
Reply With Quote
  #4  
Old 05-13-2004, 09:00 AM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Ok I did some searching... This is yours:


SELECT DISTINCTROW myTable.Field1, myTable.Name, myTable.ID
FROM myTable RIGHT OUTER JOIN Table1 ON myTable.ID = Table1.ID
WHERE (((myTable.SomeField) Is Null))


but I'm not sure how to implement it. Could you give me a mock up of the use of this code where the table is myTable the DB name is myDB for both DBs and the two drives they are stored on are c:\ and d:\
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #5  
Old 05-13-2004, 09:00 AM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

So you are using Access..ug, that complicates it a little bit. Well, I would not reccomend doing a JOIN across a network (two databases)if using Access. It is possible to do an INSERT INTO, SELECT * INTO, JOIN within one sql statment (referencing both mdb files) but I wouldn't recommend trying a JOIN over a network. It would be painfully slow. This is what I would suggest:

1) Upgrade to MSDE or SQL Server (sorry had to say it, LOL)!

2) OK, then get the current version of the network DB. You could do it like so.

Code:
'open your ADO connection Dim cn As ADODB.connection Dim strString As String Set cn = New ADODB.connection 'inserts data into myTable by referencing another mdb file, if password protected, pass it too. strString = "SELECT * INTO TempTable FROM [MS Access;DATABASE=T:\Text2.mdb;PWD=pw].[NetworkTable1]" cn.connectionString = "YOUR ADO CONNECTION TO THE local db" cn.open cn.execute strString cn.close Set cn = Nothing

3)Now, this would connect to your network database and create a temp copy on the local CPU. Now, this creates the tempTable for you, do not create the table first.'

Now, write the union query...this will combine all data and it does an implied Distinct scan. Since you structure is the same, just like this:

Code:
"SELECT * FROM TempTable UNION Select * FROM localTable"

4)This will combine the TempTable you brought down from the Network and your local copy.

Now, to send data back and forth, just run a distinctrow query against this query.

Code:
"SELECT DISTINCTROW from myQuery"
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #6  
Old 05-13-2004, 01:53 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

not sure how to use this part:
Quote:
Now, to send data back and forth, just run a distinctrow query against this query.

Code:
"SELECT DISTINCTROW from myQuery"

what is myQuery? when did we declare it and what does it contain?

Also when I run the first bit more than once it tells me that tempTable already exsists
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #7  
Old 05-13-2004, 02:15 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Myquery is the saved Union query I described above
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #8  
Old 05-13-2004, 02:25 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by MKoslof
Myquery is the saved Union query I described above
So if I understand correctly my code should look like this
Code:
Private Sub frmUpload_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cn As ADODB.Connection Dim strString As String Dim myQuery As String cn = New ADODB.Connection strString = "SELECT * INTO TempTable2 FROM [MS Access;DATABASE= \\Eloi\database\Data Tables.mdb].[tbl Contacts]" cn.ConnectionString = strDBConnection cn.Open() cn.Execute(strString) cn.Close() cn = Nothing cn = New ADODB.Connection myQuery = "SELECT * FROM TempTable UNION Select * FROM [MS Access;DATABASE= c:\Data Tables.mdb].[tbl Contacts]" strString = "SELECT DISTINCTROW from myQuery" cn.ConnectionString = strDBConnection cn.Open() cn.Execute(strString) cn.Close() cn = Nothing End Sub
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #9  
Old 05-13-2004, 02:56 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

ok. this didn't work. and I got the message about the table already existing again
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #10  
Old 05-13-2004, 03:01 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Almost:

your second query doesn't need the database name. Since you copied all the data from the network mdb file to the local one..just name the tables

Code:
myQuery = "SELECT * FROM TempTable UNION Select * FROM OtherTable"

And actually more I think about it..you don't need the final query.

Since your two tables are exactly the same..just run the UNION query and look at your results. I believe if you had 200 records in your TempTable and 23 new records in the local table..this Union Query would return 423 unique records..check it .
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #11  
Old 05-13-2004, 03:06 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

So, in a nutshell:

Code:
Private Sub frmUpload_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cn As ADODB.Connection Dim strString As String Dim myQuery As String Dim rs As ADODB.recordset cn = New ADODB.Connection strString = "SELECT * INTO TempTable2 FROM [MS Access;DATABASE= \\Eloi\database\Data Tables.mdb].[tbl Contacts]" cn.ConnectionString = strDBConnection cn.Open() cn.Execute(strString) cn.Close() cn = Nothing cn = New ADODB.Connection rs = New ADODB.recordset 'this returns a recordset myQuery = "SELECT * FROM TempTable2 UNION Select * FROM tbl_Contacts" cn.ConnectionString = strDBConnection cn.Open() rs.Open query, cn, adOpenkeyset, adLockOptimistic, adCmdText 'do something with your combined, distinct records rs.close cn.Close() rs = Nothing cn = Nothing End Sub
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

Last edited by MKoslof; 05-13-2004 at 03:23 PM.
Reply With Quote
  #12  
Old 05-13-2004, 03:16 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Now I am lost.... I find no reference to the local directory in this code.

when you said :
Quote:
3)Now, this would connect to your network database and create a temp copy on the local CPU
by CPU did you mean hard drive?
Is this code creating my local copy of the db as well?
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #13  
Old 05-13-2004, 03:19 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Oh wait...
strDBConnection refers to the current DB connection therefore cant be used for cn.connectionstring in the second instance (it will just go back and loock at the network stored original again)
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #14  
Old 05-13-2004, 03:25 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

No.

In your FIRST connection you connect to the local mdb file. Then in your select into code (look at it again), you reference the network database, then you bring in the data to a new table locally. Then you do cn.close, set cn = Nothing. That is fine, but you don't have to do that.

We never opened a connection to the network DB. You only create the temp table off of the network data, that's it. That is its only purpose.

Then, you open a recordset locally, UNIONING your local table and the temp table you just created.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #15  
Old 05-13-2004, 03:28 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Mapped out:

1) create an ADO connectionstring to your C drive database
2) Via the SELECT * INTO you reference the network database and create a temp table.
3) Now you have a temp table and your local table on the C drive.
4) Now create a recordset object and do an rs.Open. UNION your local table and temp table just created.
5) Close the recordset and close your ADO connection
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #16  
Old 05-13-2004, 03:29 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

AAAAUUUUUGGGHH!


Now that I got that out of the way:

ok, it keeps trying to find my network copy starting in the project directory. Then reporting the error that it cant find c:\Documents and Settings\me\My Documents\....DB.Mdb
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #17  
Old 05-13-2004, 03:32 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

what is your connection string? Just hard code it. If you are using an environmental variable, it is going to look in the project directory for your database.

If you local database is in C:\VBSucks, just hard code the connection string for now, so you can get the process done.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #18  
Old 05-13-2004, 03:38 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Quote:
Originally Posted by MKoslof
what is your connection string? Just hard code it. If you are using an environmental variable, it is going to look in the project directory for your database.

If you local database is in C:\VBSucks, just hard code the connection string for now, so you can get the process done.
I have hard coded everything now and it still keeps looking in the my documents directory for the stupid thing. What's weird is that the first few times when the rest of the code was messed up this part ran like a song.
__________________
Win Win Situation: Swapping my salary with my golf score
Reply With Quote
  #19  
Old 05-13-2004, 03:41 PM
MKoslof's Avatar
MKoslofCompare two tables MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Well, can't help you there. I don't see anywhere in this code piece where this would happen. Something else in your code or environmental settings is causing .Net to look in your project directory. Not sure. Good luck
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #20  
Old 05-13-2004, 03:42 PM
VisuallyImpared's Avatar
VisuallyImpared VisuallyImpared is offline
Junior Contributor
 
Join Date: Sep 2002
Location: Toronto, Canada
Posts: 278
Default

Here is the error:
(Eloi is the Network location of the DB)

[QUOTE=Additional information: 'C:\Documents and Settings\Eric Reid\My Documents\Visual Studio Projects\Service and Support Record Centre\bin\ \Eloi\i\Database\Eloi\Data Tables.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.QUOTE]


here is the code :
Code:
Private Sub frmUpload_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim cn As ADODB.Connection Dim strString As String Dim myQuery As String Dim rs As ADODB.Recordset cn = New ADODB.Connection strString = "SELECT * INTO TempTable6 FROM [MS Access;DATABASE= \\Eloi\i\Database\Eloi\Data Tables.mdb].[tbl Contacts]" cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= \\Eloi\i\Database\Eloi\Data Tables.mdb ;Persist Security Info=false;" cn.Open() 'IT FAILS AT THIS LINE V cn.Execute(strString) cn.Close() cn = Nothing cn = New ADODB.Connection rs = New ADODB.Recordset 'this returns a recordset myQuery = "SELECT * FROM TempTable UNION Select * FROM tbl_Contacts " cn.ConnectionString = strDBConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\Eloi\i\Database\Eloi\Data Tables.mdb ;Persist Security Info=false;" cn.Open() rs.Open(myQuery, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, ) 'do something with your combined, distinct records MsgBox(rs.RecordCount) rs.close() cn.Close() rs = Nothing cn = Nothing End Sub
__________________
Win Win Situation: Swapping my salary with my golf score

Last edited by VisuallyImpared; 05-13-2004 at 03:50 PM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare two tables TomG Database and Reporting 4 12-03-2003 11:14 AM
SQL Query - Compare 2 Tables jmull Database and Reporting 10 10-28-2003 01:00 PM
Compare two tables PrOpHeT Database and Reporting 12 12-17-2002 12:39 PM
compare tables john8675309 Database and Reporting 0 09-11-2002 06:12 PM
Creating new tables and links scribbles Database and Reporting 11 08-30-2002 07:18 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Compare two tables
Compare two tables
Compare two tables Compare two tables
Compare two tables
Compare two tables
Compare two tables Compare two tables Compare two tables Compare two tables Compare two tables Compare two tables Compare two tables
Compare two tables
Compare two tables
 
Compare two tables
Compare two tables
 
-->