Solved

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

Posted on 2010-08-17
4
249 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:Letsgetcoding
  • 2
4 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33462118
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
 
LVL 53

Accepted Solution

by:
Dhaest earned 250 total points
ID: 33462124
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
 

Author Comment

by:Letsgetcoding
ID: 33462215
Hey,

Access database. A having a look at these links now.
0
 
LVL 5

Assisted Solution

by:BrianVSoft
BrianVSoft earned 250 total points
ID: 33462567
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now