Connecting VB to an existing Access Database

[dawnevans]

I have been working on a VB program that is taking a bit longer than I expected (since I do it in my free time-not at work). The program is to manage classes offered in a local jail. I would like to get my client started by giving him the access database to start using now since he is currently doing all his work manually. <br>
<br>
What do I need to do once I finish my VB program (and package and deploy) so that he can use already existing information and that it does not get deleted?

 

[VB400]

What is the relationship between the Access DB and the new VB program?

 

[dawnevans]

I indend to use the Access DB as the data source with all the front end forms, etc. to be handled by VB. Is that what you mean?

 

[peekay]

I presently have solved the same problem in one of my programs. Your program must check whether there is an existing file (open it) by that name. Should you get an error you can use the cdl control to enable him to choose his own database. Just make sure the two databases have identical tables and fields otherwise you wil get another error - which you can also trap. Should the name of this database differ from yours then you can save the path and filename in an .ini text file and read it every time he opens the program. I can give you some of my code that accomplishes this. <br>
<br>

 

[dawnevans]

I would really appreciate it if you could give me the code. Thanks for your help.

 

[peekay]

'This routine checks whether:<br>
<br>
'there is a connected valid database<br>
If HerbaPathandFilename &lt;&gt; &quot;&quot; Then OpenExistDatabase: Exit Sub 'there is a valid path and file name in this variable<br>
<br>
'or whether an existing one must be browsed and connected<br>
If HerbaPathandFilename = &quot;&quot; Then<br>
Response = MsgBox(&quot;There is no presently no database connected to this program - do you wish to look for one&quot;, vbYesNo)<br>
If Response = vbYes Then<br>
GetHerbaPathandFilename<br>
OpenExistDatabase<br>
Exit Sub<br>
Else<br>
Response = MsgBox(&quot;Do you wish to start a new database from scratch&quot;, vbYesNo)<br>
If Response = vbYes Then<br>
MakeNewDatabase<br>
OpenExistDatabase<br>
Exit Sub<br>
Else<br>
MsgBox &quot;Can't continue without database&quot;, vbOK: End<br>
End If<br>
End If<br>
End If<br>
End<br>
<br>
Exit Sub<br>
<br>
ErrorHandler:<br>
'there may not be a database<br>
<br>
If Err = 3078 Then<br>
Response = MsgBox(&quot;Database is not in the correct format - do you wish to create a new database&quot;, vbYesNo) 'the database is not in the correct format<br>
If Response = vbYes Then<br>
dbHerbal.Close<br>
MakeNewDatabase<br>
Resume<br>
Else<br>
End<br>
End If<br>
End If<br>
Resume Next<br>
End<br>
<br>
'unknown error<br>
ErrorRoutine = &quot;Initialise&quot;<br>
ErrorType = Err + Error<br>
frmFatal.Show<br>
End<br>
End Sub<br>
Public Sub OpenExistDatabase()<br>
Dim Response<br>
On Error GoTo ErrorHandler<br>
Set dbHerbal = OpenDatabase(HerbaPathandFilename) 'if an error(3024) is generated here the pathandfilename is incorrect<br>
<br>
'find out whether it is a valid database and the correct version<br>
Set rstCust = dbHerbal.OpenRecordset(&quot;Customers&quot;, dbOpenDynaset) 'if an error(3078) is generated here if the table is incorrect<br>
Set rstProducts = dbHerbal.OpenRecordset(&quot;Products&quot;, dbOpenDynaset) 'if an error(3078) is generated here if the table is incorrect<br>
Set rstSales = dbHerbal.OpenRecordset(&quot;Sales&quot;, dbOpenDynaset) 'if an error(3078) is generated here the table is incorrect<br>
Exit Sub<br>
<br>
<br>
ErrorHandler:<br>
If Err = 3024 Then 'the file could not be found<br>
Response = MsgBox(&quot;The database file could not be found - do you wish to look for it&quot;, vbYesNo)<br>
If Response = vbYes Then<br>
GetHerbaPathandFilename<br>
Resume 0<br>
Else<br>
Response = MsgBox(&quot;Do you want to open a new database&quot;, vbYesNo)<br>
If Response = vbYes Then<br>
MakeNewDatabase<br>
Resume 0<br>
Else<br>
MsgBox &quot;The program cannot function without a database - either use an existng database or create a new one&quot;<br>
End<br>
End If<br>
End If<br>
End If<br>
If Err = 3078 Then<br>
Response = MsgBox(&quot;Database is not in the correct format - do you wish to create a new database&quot;, vbYesNo) 'the database is not in the correct format<br>
If Response = vbYes Then<br>
dbHerbal.Close<br>
MakeNewDatabase<br>
Resume 0<br>
Else<br>
MsgBox &quot;The program cannot function without a database - either use an existng database or create a new one&quot;<br>
End<br>
End If<br>
End If<br>
Stop 'unhandled error<br>
End<br>
End Sub<br>
<br>
<br>
Public Sub GetHerbaPathandFilename()<br>
frmMain.cdlDatabase.Filter = &quot;Herbadata files¦*.mdb&quot;<br>
frmMain.cdlDatabase.ShowOpen<br>
HerbaPathandFilename = frmMain.cdlDatabase.FileName<br>
'store the filename in the .ini file<br>
Open &quot;c:\Herbadata\Herbadata.ini&quot; For Input As #1 'if opening this file creates an error no file exists - errorhandler<br>
'there was a file by this name<br>
Input #1, HDD<br>
Input #1, AuthoCode<br>
Close #1<br>
Open &quot;c:\Herbadata\Herbadata.ini&quot; For Output As #1<br>
Print #1, HDD<br>
Print #1, AuthoCode<br>
Print #1, HerbaPathandFilename<br>
Close #1<br>
End Sub<br>
<br>
Function CurrentDirectory() As String<br>
HerbaDirectory = String(145, Chr(0))<br>
HerbaDirectory = Left(HerbaDirectory, GetCurrentDirectory(145, HerbaDirectory))<br>
End Function<br>