Working with Databases II

Working With Databases II

There will come a time when you may need to make changes to your application. Or you want to add new features to your application. Either way you may need to make changes to your database. When you do this you can no long just provide a patch to update your software, the customer has to install the full version of your software and some how get their data out of the old database in to the new one.

That's where this tutorial comes in. I will be providing examples and code that will allow you to either, export your customers data from the old database and import it in to the new one, or use code to alter the database. Either way it will give your customer an alternative to manually re-entering the data in to the new database. Which believe me, will make your customers very happy.

I will be using VB5/6 and Access 97 to test all the code examples for this tutorial. If you haven't done so you might want to take a look at the Hands On project "Working with Databases" that I posted a few months back. You should also note that the syntax for your database my very so check you database manual for the proper syntax for your database. I will however try when ever possible to us code that will work on all databases, but sometimes that is not possible.

We will be using DML (The Data Manipulation Language) to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database.

First up Exporting

Exporting Data I

When changing the underling structure of a database you have two choices, actually three. One provide your customer with the option to export the data from the old database to the new one. Two, alter the existing database so the customer has doesn't have to do anything. The third I wouldn't recommend, have your customer manually re-enter all the records in the new database.

We will first look a exporting data out of the current database. There are several ways to do this, you can pick the one that most matches your situation. This can be done using DAO or ADO and most of these examples can be applied to any database. When ever possible I will your standard SQL syntax.

The first example I will use DAO to export records out of a database in to a comma delimited file. A comma delimited file is a text file that separates the records by using a comma, each comma represents a new record. The file is then saved in a directory that you specify and can then later be imported in to a different database.

This example uses the SQL INTO method to copy the records out of a database and save them to a csv(Comma Separated Variable)file.

Public Sub Main()
On Error Resume Next Dim db As Database

Set db = OpenDatabase("C:\Program Files\VB\biblio.mdb")
db.Execute "SELECT * INTO [TEXT;DATABASE = C:\MyDocuments].[test.csv] FROM [Authors]"
If Err.Number <> 0 Then 'Always check this!!!
MsgBox Err.Number & vbCr & Err.Description
End If
End Sub

The above code will export all the records from the table Authors in the a file called test.csv located in the My Documents folder.

There is one thing to remember since you use comma's to create your csv file. You must make sure that the records that are being exported can not have any comma's in them. An example of this is if you exporting lets say an employee's name or address you can not have a comma in them.

This will case error - John Smith, III
This will case error = 123 Elm Street, Apt# 123

So if you use this method make sure you have your customer remove any comma's that may be in the records that are being exported.

Exporting Data II

Private Sub ExportDB()
Dim strInFile As String
Dim intInFile As Integer ' file handle
Dim strOutFile As String
Dim intOutFile As Integer
Dim i As Integer
Const SkipLines As Integer = 1
Dim strOneLine As String

strInFile = "C:\My Documents\Log.txt"
strOutFile = "c:\temp\temp.txt"

' delete out out file
If Dir(strOutFile) <> "" Then
Kill strOutFile
End If

intInFile = FreeFile()
Open strInFile For Input As intInFile

intOutFile = FreeFile()
Open strOutFile For Output As intOutFile

' here skip some lines...I skip the first two, but then you might need to
'change your transfer text to not expect field names...this is your choice...

For i = 0 To SkipLines
Line Input #intInFile, strOneLine
Next i

Do While EOF(intInFile) = False
Line Input #intInFile, strOneLine
Print #intOutFile, strOneLine
Loop

Close intInFile
Close intOutFile

End Sub

This code will allow you to take a text file and adjust it. Lets say that you do not want the first records or the first 3 records then you can use this code to skip those records. If you want to skip the first 3 records then in the Const SkipLines As Integer enter 3.

Const SkipLines As Integer = 3

Exporting Data III

Private Sub ADOExport()
Dim oCn As New ADODB.Connection
Dim oRs As New ADODB.Recordset

'This is the connection to the database. This used MS SQL server but it can 'be any database.
oCn.Open "provider=sqloledb;data source=(local);user id=me;initial catalog=northwind;"

'This select all the records in the Categories table

oRs.Open "SELECT * FROM CATEGORIES", oCn

'This is used to hold all the records from the Categories table

Open "c:\categories.txt" For Output As #1 ' Open file for input.

'This actually writes the records to the categories text file.

Do Until oRs.EOF
Write #1, oRs("CategoryID").Value, oRs("CategoryName").Value ' oRs.MoveNext
Loop

oRs.Close
oCn.Close
Set oRs = Nothing
Set oCn = Nothing

End Sub

This basically does the same thing as our first example. It opens up the database and then exports the records in to a text file called categories.txt.

Exporting Data IV

Private Sub ExpAmtPaid()
Dim db As Database
Dim rs As Recordset
Dim strFile As String
Dim intFile As Integer
Dim strFormat As String

strFormat = "mm/dd/yy"
strFile = "C:\Windows\Temp\IGBackup\amtpd.csv"
intFile = FreeFile
Open strFile For Output As intFile

'The path to the database is held in the txtBackup text box. This 'database requires a password.
Set db = OpenDatabase(txtBackupDB, False, False, ";pwd=mypwd")
Set rs = db.OpenRecordset("SELECT * FROM AmountPaid")

With rs
Do While Not .EOF
Print #intFile, !custInv; ","; !CustID; ","; !amtPaid; ","; Format$(!PayDate, strFormat); ","; !Paid .MoveNext
Loop

.Close
End With

Set rs = Nothing
Set db = Nothing
Close intFile
End Sub

The above code exports the records in to the amtpd.csv file. You will notice how to deal with dates when exporting. Since you are using a text file you need to change the date in to a string in order to export the date.

Importing Data I

This week we will learn how to import records from the csv and txt files in to the database. If everything went well while you were exporting your files then you should have any problems importing the records.

The code below uses the MS Access Object Model to import data in to an Access 97 database.

Private Sub ImportDB()
Dim objAccess
Dim strPathToMDB
Dim strTableName
Dim strPathToTextFile

Const acImportDelim = 0

strPathToMDB = "C:\TestDB.mdb"
strPathToTextFile = "C:\Test.txt"
strTableName = "Attach"

' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.8")

' For Access 2000, use Application.9
'Set objAccess = CreateObject("Access.Application.9")

' Open the desired database
objAccess.OpenCurrentDatabase(strPathToMDB)

' Use the TransferText command to import the file (with Column Heads)
objAccess.DoCmd.TransferText acImportDelim,,strTableName,strPathToTextFile=,True

' Clean up
Set objAccess = Nothing
End Sub

Importing Data II

This code uses ADO to import records in to a database.

Private Sub Import()
Dim Conn As New ADODB.Connection

'Please specify the location of your destination Access database here
Conn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=C:\test.mdb"

'Please specify the Table name and location and the name of your Text
'file accordingly. You should also verify the format of the Text file
'and whether it includes the field names in the first record

strSQL="insertintoTable1select*from[Text;Database=C:\;HDR=YES;FMT=Delimited].[textfile.txt]"

Conn.Execute strSQL

Conn.Close
Set Conn = Nothing
End Sub

The above code copies the record out of the textfile.txt and imports then in to the text.mdb.

Importing Data III

He is another version of a import code.

Private Sub ImportDB2()
Dim cn As ADODB.Connection
Dim rs As Recordset

Set cn = New Connection
Set rs = New Recordset

'open connection to folder that contains the CSV file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties=Text;"

'open the file
rs.Open "select * from test.csv", cn
'we have data
MsgBox rs.Fields(1).Value

End Sub

Importing Data IV

This code example uses the Input statement to import records in to an Access database.

This example assumes that test.csv is a file with a few lines of data written to it using the Write # statement; that is, each line contains a string in quotations and a number separated by a comma, for example, ("Hello", 234).

Private Sub ImportDB()
Dim db As Database
Dim rec As Recordset
Dim fn as Integer

Set db = Opendatabase("C:\text.mdb", False, False)
Set rec = db.Openrecordset("tableName")

fn = FreeFile
Open "C:\test.csv" For Input As #Fn ' Open file for input.
Do While Not EOF(Fn) ' Loop until end of file.
rec.Addnew
Input #Fn, rec.Fields(0), rec.Fields(1), rec.Fields(2)
rec.Update
Loop

Close #fn
rec.Close
db.Close
End Sub

Importing Data V

This will complete the importing and exporting of data to and from a Database. The code below uses the Open statement to import records from a csv file to the database.

Private Sub ImportAmtPaid()
On Error Resume Next
Dim db As Database
Dim rs As Recordset
Dim CSVname As String
Dim fileName As Integer
Dim cName As Long
Dim cID As String
Dim cPaid As Currency
Dim pdDate As String
Dim pdYN

'The txtNewDB holds the path to the database
Set db = OpenDatabase(txtNewDB, False, False, ";pwd=pwd")
Set rs = db.OpenRecordset("TableName", dbOpenDynaset)

fileName = FreeFile
CSVname = "C:\Windows\Temp\test.csv"
Open CSVname For Input As #fileName
Do Until EOF(fileName)

Input #fileName, cName
Input #fileName, cID
Input #fileName, cPaid
Input #fileName, pdDate
Input #fileName, pdYN

'Now add records to the database
With rs
.AddNew
!custInv = cName
!CustID = cID
!amtPaid = cPaid
!PayDate = CDate(pdDate)
!Paid = pdYN
.Update
End With
Loop
Close #fileName

rs.Close
db.Close
End Sub

There are two things that you should note. One remember that when we exported the date out of the old database we do so as a string. Now that we are importing the date record it must then be converted back to a date format, that is why the CDate is used.

The second thing is when importing a Boolean field it will only work if is its not declared. Meaning the variable must be a variant, if you type the following you will get a error when trying to import a Yes/No field.

Dim pdYN As Booleen
S/B
Dim pdYN

Altering a Database

While using the importing and exporting option to transfer data from one database to another can resolve the problem of changing a database. In some cases it may not be very efficient. If all you have are a couple of tables to export and each table have a couple of fields then physically exporting and importing records should not be a problem. However if you have a lot of tables in your database that need to me exported due to a change in the database then the best alternative would be to alter the table in lieu exporting all the records out of the database.

This portion of our tutorial on Working With Databases, well will explore the Alter statement for updating or changing a database.

Alter Database - Adds or removes files and filegroups from a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file. ALTER DATABASE provides the ability to change the database name, filegroup names, and the logical names of data files and log files.

Remember I'm using Access 97 for these examples, the syntax my slightly very depending on which database you are using. Check your database manual for the proper syntax for your database.

Altering Using Add and Drop

The Alter statement has these parts:

Table - The name of the table to be altered. field - The name of the field to be added to or deleted from table.
type - The data type of field.
size - The field size in characters (Text and Binary fields only).
index - The index for field.
multifieldindex - The definition of a multiple-field index to be added to table.
indexname - The name of the multiple-field index to be removed.

ADD

The Add part of the Alter statement will allow you to add a column to a table. This can be used when a feature you added to your application requires you to have a new field in the database table. You can use ADD COLUMN to add a single field to a table by specifying the field name, data type and, optionally, a size for the Text and Binary fields: The Add portion of the Alter statement looks like this.

ALTER TABLE Customer ADD COLUMN PhoneNum Text(15);

The above code will add a column to the Customer table named PhoneNum that has the data type of Text and size of 15 characters.

You can add the reserved words NOT NULL to require valid data to be added to that field:

ALTER TABLE Sales ADD COLUMN UnitPrice CURRENCY NOT NULL;

Foreign Key

The Foreign Key portion of the Alter statement allows you to add a Foreign Key to a field. The Foreign Key statement looks like the following.

Alter Table Customer Foreign Key (CustID)

The above code will add a Foreign Key to the CustID field of the Customer table.

Drop

The Drop portion of the Alter statement allows you to remove a column for a table. You can also drop a PrimaryKey using the Drop statement. The Drop statement looks like the following.

"Alter Table Customer Drop Column MName" "Alter Table Customer Drop constraint PrimaryKey"

The above code will remove the MName column in the Customer table. The second statement will remove the PrimaryKey on the Customer table.

Adding Columns (Fields) using ADO, DAO

Our last demo showed how to add a field to an existing table in your database using the Alter statement. This will show you have to add a new fields using DAO CreateField and ADO Append.

Access 97 (Microsoft DAO 3.51 object library is reference by default)

Public Sub CreateNewColumn()
Dim db As Database
Dim tbl As TableDef

'This method will add a field to the database that you are currently working in
Set db = Opendatabase(App.Path "\your.mdb", False, False")
'access the tables collection and the table to append a field to
Set tbl = db.TableDefs("NewTable")
'append a field to the table using its object variable

With tbl
.Fields.Append .CreateField("NewTextField", dbText)
End With

db.Close
End Sub

The above code will create a field called NewTextField and it as stated by the name it will have a data type of Text.

Example 2

Using DAO objects you can add/delete to the Fields collection of the table (VB4 or VB5):

Sub AppendDeleteField(tdfTemp As TableDef, strCommand As String, strName As String, _
Optional varType, Optional varSize)

With tdfTemp

' Check first to see if the TableDef object is
' updatable. If it isn't, control is passed back to
' the calling procedure.
If .Updatable = False Then
MsgBox "TableDef not Updatable! " & "Unable to complete task."
Exit Sub
End If

' Depending on the passed data, append or delete a field to the Fields collection of the specified ' TableDef object.
If strCommand = "APPEND" Then
.Fields.Append .CreateField(strName, varType, varSize)
Else
If strCommand = "DELETE" Then .Fields.Delete strName
End If

End With
End Sub

You can use the ADOX (ADO for DDL and Security) library; it exposes a "catalog" object that contains a collection of tables, which in turn contain a collection of columns, which have data types. Depending on the capabilities of the OLEDB provider, you can add, change and delete these items to modify your database schema.

Using Microsoft ActiveX Data Objects (ADO 2.1 and up) . The code to add a field to a table with ADO is as follows:

Public Sub CreateNewColumn()
Dim tbl As New ADOX.table
Dim idx As New ADOX.Index
Dim oCol As New ADOX.Column

On Error GoTo err

'Adding table and field to it:
Dim TCat As New ADOX.Catalog
Dim tbl As New ADOX.table
Dim oCol As New ADOX.Column

tbl.name = < table name>
TCat.Tables.Append tbl

With tbl
.Columns.Append "field_name1", adVarWChar, 50
.Columns.Append "field_name2", adInteger
End With

TCat.Tables(< tablename>).Columns.Append oCol
End Sub

The above code will add two columns to an existing database. The first added column with a data type of Text with 50 characters and the second a data type of Integer.

Putting it all together

Over the last couple of weeks we I have provided code sample that allowed you to export data from a database and import data to a database. I have also provided code samples that allowed you to alter an existing database. This portion of the tutorial will put it all together and provide you with a real example of importing and exporting and altering a database.

A few months ago I was presented with the dilemma of providing a tool for my customers to update our software. I had made some changes to the application that add new features and by doing so I had also changed some tables in the database. So I took up the task of developing a application that would export the data in the users current database and import it to the new database. The results was a program called DBTools. By the time I finished coding for all the tables that had to be changed I thought, there must be an easier way. The Alter statement was the answer, it allows you to change the database with out exporting all the records and then importing to the new database. It allow you to change the existing database, there by making the change a lot easier for you and the customer.

DBtools Export

The code below was used to export database out of an existing database. I used coma delimited method to export the records in to a csv file. Before you can save the cust.csv file you have to create a temp directory to hold the file. Once the import has taken place you would then delete the cust.csv file and delete the temp directory.

Private Sub Form_Load()
On Error Resume Next
MkDir "C:\Windows\Temp\IGBackup"
End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
Kill "C:\Windows\Temp\IGBackup\*.csv"
RmDir "C:\Windows\Temp\IGBackup"
End Sub

Using the above code a directory was created called IGBackup this will hold all the csv files that will be saved. Once the import portion is complete all the csv files are deleted and the directory deleted. It's always good to clean up your mess. Remember the temp directory is just that, all the files in there should be temporary. Too bad some programmer forget that and leave files behind. Which is way I have to constantly clean out the temp folder.

Private Sub ExpCustomer()
Dim db As Database
Dim rs As Recordset
Dim strFile As String
Dim intFile As Integer
Dim strFormat As String

strFormat = "mm/dd/yy"
strFile = "C:\Windows\Temp\IGBackup\cust.csv"
intFile = FreeFile
Open strFile For Output As intFile
Set db = OpenDatabase(txtBackupDB, False, False, ";pwd=pwd")
Set rs = db.OpenRecordset("SELECT * FROM CUSTOMER")

With rs
Do While Not .EOF
Print #intFile, !CustID; ","; !CustName; ","; !CustAdd; ","; !CustCity; ","; !CustState; ","; !CustZip; ","; !CustPhone; ","; !CustFax; ","; !CustEmail; ","; !Resale; ","; _ !DefaultTaxRate; ","; !CreditHold; ","; !CreditLimit; ","; !Inactive; ","; !CompID; ","; Format$(!PostDate, strFormat)
.MoveNext
Loop

.Close
End With

Set rs = Nothing
Set db = Nothing
Close intFile
End Sub

The above code exports the records in the Company table and saves them to a csv filed called "cust.csv". Once exported the file will look like this.

AAT-136,Amy Smphat,2601 Nonesuch Rd,Abilene,TX,79606,000 000-0000,,am124@aol.com,, 0 ,False, 50 ,False,crsk,04/19/02
ABY-131,Anthy Kiby,3705 Some Dr.,West Valley,UT,84119,000 000-0000,,aby21@yahoo.com,, 0 ,False, 50 ,False,crsk,04/11/02
ACO-116,Antony Todco,1800 S NE,Albuquerque,NM,87112,505 269-0939,,tte717@aol.com,, 0 ,False, 50 ,False,crsk,02/17/02

The comma's represent a change in records. Using the above example the first records is the customer's ID number the next record is the customer's name the third the customer's address and so on. This is way when using this type of code the records can not contain any commas, or it will through off the records during the import causing errors.

The txtBackupDB holds the path to the existing database.

DBTools Import

The code below imports the records stored in the cust.csv file in to the new database.

Private Sub ImportCustomer()
'On Error Resume Next
'On Error GoTo errhand
Dim db As Database
Dim rs As Recordset
Dim CSVname As String
Dim fileName As Integer
Dim cCID As String
Dim cName As String
Dim cAdd As String
Dim cCity As String
Dim cState As String
Dim cZip As String
Dim cPhone As String
Dim cFax As String
Dim cEmail As String
Dim cResale As String
Dim taxRate As Double
Dim cHold
Dim cLimit As Currency
Dim cInactive
Dim cCompID As String
Dim posDate As String

Set db = OpenDatabase(txtNewDB, False, False, ";pwd=pwd")
Set rs = db.OpenRecordset("CUSTOMER", dbOpenDynaset)
'Problem with the transfer, if the invoice was not paid then it skips that record instead of entering it as "".

fileName = FreeFile
CSVname = "C:\Windows\Temp\IGBackup\cust.csv"
Open CSVname For Input As #fileName
Do Until EOF(fileName)
Input #fileName, cCID
Input #fileName, cName
Input #fileName, cAdd
Input #fileName, cCity
Input #fileName, cState
Input #fileName, cZip
Input #fileName, cPhone
Input #fileName, cFax
Input #fileName, cEmail
Input #fileName, cResale
Input #fileName, taxRate
Input #fileName, cHold
Input #fileName, cLimit
Input #fileName, cInactive
Input #fileName, cCompID
Input #fileName, posDate

'Now add records to the database
With rs
.AddNew
!CustID = cCID
!CustName = cName
!CustAdd = cAdd
!CustCity = cCity
!CustState = cState
!CustZip = cZip
!CustPhone = cPhone
!CustFax = cFax
!CustEmail = cEmail
!Resale = cResale
!DefaultTaxRate = taxRate
!CreditHold = cHold
!CreditLimit = cLimit
!Inactive = cInactive
!CompID = cCompID
!PostDate = posDate
.Update
End With
Loop

Close #fileName
rs.Close
db.Close

'errhand: MsgBox Err.Number & ": " & Err.Description
End Sub

Example Alter

This examples uses the Alter statement to change the size of a field in the Barcode table. Because I was using Access 97 I had to create a temporary table, move the records to the temp table. Create a new table that changed the data type of one of the fields, then copied the records from the temporary table to the new one. I have commented the steps I took on the code it self.

Option Explicit

Private Sub Command1_Click()
Dim db As Database
Dim sql As String
Dim sql2 As String
Dim sql3 As String
Dim sql4 As String
Dim sql5 As String
Dim sql6 As String
Dim sql7 As String
Dim sql8 As String
Dim sql9 As String

'Text1 holds the path to the database
Set db = OpenDatabase(Text1, False, False, ";pwd=pwd")

'Create a temporary table to hold the records
sql = "ALTER TABLE Barcode ADD COLUMN Temp Text(15)"

'Copy the records from the UPCNo field to the temp field
sql2 = "Update Barcode Set Temp = [UPCNo]"

'Now remove the primary key so we can drop the UPCNo field
sql3 = "Drop Index PrimaryKey On Barcode"

'This table has two primary keys so drop the second one
sql4 = "Drop Index PartNo on Barcode"

'Now drop the old UPCNo field
sql5 = "Alter Table Barcode Drop Column UPCNo"

'Now create a new field with the updated text size
sql6 = "Alter Table Barcode Add Column UPCNo Text (15)"

'Now copy the records in the New UPCNo field
sql7 = "Update Barcode Set UPCNo = [Temp]"

'Now drop the Temp field
sql8 = "Alter Table Barcode Drop Column Temp"

'Now create a Primary Key and Index for UPCNo with no duplicates
sql9 = "CREATE UNIQUE INDEX PrimaryKey On Barcode (UPCNo)WITH PRIMARY"

db.Execute sql
db.Execute sql2
db.Execute sql3
db.Execute sql4
db.Execute sql5
db.Execute sql6
db.Execute sql7
db.Execute sql8
db.Execute sql9

db.Close
MsgBox "Update done!"
End Sub

If I was using Access 2000 then the above process would have been done with one sql statement.

sql = "Alter Barcode Alter Column UPCNo Text(15)"

That would have been nice but Access 97 does not have the Alter Column method just Add and Drop Column.

In Conclusion

Whether you are creating, updating or changing a database this can easily be done using VB.