Access/database

[leehor]

I am writing a stand alone program that retrieves data from
a database, from Access. When users install the program into their computer The program works fine with computers having
Access installed. But does not work when Access is not installed.

Does anyone know how I can rectify this problem?
Is it possible to use the database without installing access.
Am I missing some type of library?
Thanks,
Steve

[Lothar Haensler]

>Is it possible to use the database without installing access.

You do not need Access installed on the client's computer.
You do need to install your database interface, though, e.g. DAO or ADO.
Setup Wizard or PDWizard (VB6) usually take care of that.
Create a setup program using these tools. The resulting setup should include all data access components.

[leehor]

Thanks,
But if setup wizard doesn't take care of it. Is there a way around it?

[Spectre]

Step 1:

To use an Access database without having Access on the machine you must first (in your VB project) go to Project/References and select the reference to Microsoft ActiveX Data Objects <Version #> Library. Depending upon which version of MDAC (Microsoft Data Access Components) that you have on your machine. Mine has versions 2.0 (installed with Visual Studio 6.0) and 2.1 (installed with MS Office 2000). Then you need to declare the following objects:


public dbConnection as new ADODB.Connection
public dbCommand as new ADODB.Command
public rsRecordSet as new ADODB.Recordset




Now set up a variable to use for your SQL Statements


public SQLString as string




These objects (exposed by the reference to the Data Objects Library that we set earlier) are used to communicate with your database file. Next - and probably the hardest part - you must figure out the proper connection string to use with your connection object to define to the object what database file to talk to. I would suggest using the built in data environment to determine this. To use the Data Environment go to Project/Components when the dialog appears select the second tab (Designers) and make sure that Data Environment is checked. Once it is checked click on Apply and exit this dialog. Next in the Project Explorer right click on the project name, this will give you a popup menu - select Add/More ActiveX Designers\Data Environment. This will add a data environment object to the project and a data designer window will appear. Right click on the Connection (default name is Connection1) and from the dropdown select Properties. on the first tab select the OLEDB provider to use (for an Access database I selected Microsoft Jet 4.0 OLE DB Provider). Click on the Connection tab.Brouse to the file that you want to connect to and select it. If there is login information for your file fill it in. If your file is set up to use no security (Default when you create a database in Access) then simply delete the default entry for User Name (Admin) and click on the "Test Connection" button. If all has gone well you will see a dialog box that says "Test connection succeeded". Click on the Advanced tab and select the access mode that siuts your needs. I selected both ReadWrite and Share Deny None. Then click OK. Now if you have your object properties pane active you will see in the ConnectionSource property the connection string that you need to connect to your database. Copy and paste it to notepad or somewhere so that you will have it when needed. The connection string that I ended up with is as follows:


Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Projects\TerminologyTester\Terminology.mdb;Mode=Read|Write;Persist Security Info=false




Delete the Data Designer - it has done it's job and you don't want it hanging around.
Now we are ready to establish a connection to the database in code. First you must set the connection string property of your connection object and then open it:


dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Projects\TerminologyTester\Terminology.mdb;Mode=Read|Write;Persist Security Info=false"
If dbConnection.State = adStateOpen then
dbConnection.Close
End If
dbConnection.Open




Now pass the open connection to the command object:


dbCommand.ActiveConnection = dbConnection




Set up the selection criteria to use when populating your recordset object (Using Access SQL)


SQLString = "Select * From Table1"
dbCommand.CommandText = SQLString




Now if I have an Access database like the one indicated by the Source property in my connection string and that database contains a table named Table1 - I can populate my recordset using the Open method of my recordset object:


rsRecordSet.Open dbCommand, , adOpenStatic, adLockBatchOptimistic




Now test to see if we got any records back:


If rsRecordSet.RecordCount then
'Process your records here
'...




To access and navigate the recordset:


Name = rsRecordSet("Name")
rsRecordSet.MoveNext




This sets the value of the string variable Name to the value in the Name field of the first record then advances to the next record.

At this point you can modify a record in the database using the Execute method of the command object. First set up your SQL statement and pass it to the command object:


SQLString = "Update Table1 set Name = " & Chr$(34) & "Spectre" & Chr$(34) & " Where Rec_ID = 1"
dbCommand.CommandText = SQLString
dbCommand.Execute




You can insert a new record by:


SQLString = "Insert Into Table1 (Rec_ID, Name) Values (2, leehor)"
dbCommand.CommandText = SQLString
dbCommand.Execute




Don't forget to clean up your objects when you are finished:

[vbcode]
If rsRecordSet.State = adStateOpen Then
rsRecordSet.Close
End If
Set rsRecordSet = Nothing
If dbConnection.State = adStateOpen Then
dbConnection.Close
End If
Set dbConnection = Nothing
[\vbcode]

Note: It is not necessary to close and set the command object to nothing (you will get an error if you try).

Hope this helps.

Spectre