VB6 Compare Databases, Add missing fields from one database to the other

Hey,

So im making an application that is run during my programs installation. The purpose of this is to compare the database that is in the installation, with the existing database found on the computer.

If the old database is missing coumns/ tables etc which are found in the new database, they are added in.

How do I do this?

Thanks,
Jessee
LetsgetcodingAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dirk HaestProject managerCommented:
What database are we talking ? Is it a sql-server or access database ?

Examples below were found at http://techrepublic.com.com/5208-6230-0.html?forumID=87&threadID=160570&messageID=1662730&tag=content;leftCol
There are many answers to this question - here's 3 
1st: (Most likely the fastest)

Run a query of the SysObjects Table

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (MSysObjects.Name="Your Table Name") AND (MSysObjects.Type=1)

2nd: (sneaky but works)

On error resume next
err.number = 0
'Asssume your ADO data connection is cnn...
cnn.execute("DROP TABLE [YOUR TABLE NAME]")
'Discard error...
err.number = 0
'Create your table...

3rd: (Snaziest)
'Walk the Jet table collection...


Reference: (Use Late Binding if you do not want to reference)
Microsoft ActiveX Data Objects 2.x Library (This is ADODB)
Microsoft ADO Ext. 2.8 for DDL and Security (This is ADOX)


Call the fuction: msgbox TableExists("ABC")

Past the following code:

Option Explicit
'----------------------------------------------------------------
Public Function TableExists(TableName As String) As Boolean
On Error GoTo ErrorHandle:
Dim cnn As ADODB.Connection
'Dim cnn
Dim cat As ADOX.Catalog
'Dim cat
Dim tbl As ADOX.Table
'Dim tbl
'--------------------------------------------------------------
'Connect to the Database...
'--------------------------------------------------------------
Set cnn = CreateObject("ADODB.Connection")
'cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\YourDB.mdb;Persist Security Info=False"
cnn.Open
'--------------------------------------------------------------
'Set cat = New ADOX.Catalog
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = cnn
'--------------------------------------------------------------
For Each tbl In cat.Tables
If tbl.Type = "TABLE" And tbl.Name = TableName Then
TableExists = True
End If
Next
'--------------------------------------------------------------
ExitProc:
On Error Resume Next
Set tbl = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing
Exit Function
ErrorHandle:
TableExists = False
GoTo ExitProc:
End Function

Open in new window

0
Dirk HaestProject managerCommented:
Find Out Whether a Specific Column Exists in a Database Table
http://imar.spaanjaars.com/262/find-out-whether-a-specific-column-exists-in-a-database-table
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LetsgetcodingAuthor Commented:
Hey,

Access database. A having a look at these links now.
0
BrianVSoftCommented:
My guess is that you (like the rest of us)  have a long standing database app that you regularly improve by adding new fields to the tables. And, when you offer a version upgrade, you need to add any new fields to the old previous version database..
We have an upgrade app that uses a 'hard programed' list of "new fields" to check if all of them are present - if any are missing, they are created..
This App also unpacks all the new exe's (from a winzip self extractor) to the resource folder of the main app.
Whenever we add a new field to our standard database, we also add a 'hard programed' item to the above list.
It uses VB6 DAO on an Access database.. If this model suits your situation, I could post extracts from the source code.
It uses statements like..
    Set NewField = xTaDef.CreateField(FieldNam$, FType, FSize)
    NewField.AllowZeroLength = True: NewField.Required = False
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.