Connect VBapplication to Access database

[touchstone57]

Hello.

I am doing an college assignment and I am just creating a practice program to help me gain experience and a better understanding of Visual Basic 6.0

It is to do with databases. The program I am creating is meant to connect to an access
database, some of the functions of the program allow you edit and add records to the database. I have created the interface and the code in VB and made a  DB table in Access.

But for some reason VB will only allow me to connect to Access 97 version of access. When trying to connect to Access 2000 or 2003 my program gets an error and cant continue. This means I can only connect VB to Access 97, this is problematic because once you have converted the DB to access 97 format your are unable to edit the database, which is a problem because the program requires you to edit and add to the database.

So I'm asking how to fix VB so it uses the latest version access (2003)

Thanks

[Sidewinder]

Normally you would dim a new adodb.connection object and then open the connection with the provider and the dataset name of the database.

Code: [Select]

dim cn as new adodb.connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=fullpathtodbfile"

Also check that your VB version has all the service packs installed (SP6 is currrent) and that MDAC (Microsoft Data Access Components) is current [ver. 2.8]

Good luck.

[touchstone57]

Thats not how I did mine (at least thats what I wasn't shown in College) I used the data control on the left hand side on put it on my form. I then changed the properties and connected it to the database files I had stored. It seems to connect fine when in Access 97 format and allows me to look through all the data.

Here is a picture for better understanding (it shows the data control and where it is connected)

Click here

Is this perhaps the reason why it will not connect to database 2003?

Also how do I check that the latest Service pack (for Vb) and MDAC are installed?

Thanks

[Sidewinder]

There's no problem using a control to connect to the database as this binds the data to the control and saves you coding later on.

The VB version should be on the splash screen when VB loads, but for something a little less flashy, open VB-->Help-->About VB should pop up a screen with the info on versions.

For the MDAC version, check registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess\FullInstallVer

Also check Component Checker Tool

 

More likely your MDAC version is out of date, but you never know.

[touchstone57]

I'm not sure I could find anything specific on the SP, and the MDAC seems to be up to date. Here is a picture anyway.

Click here


Thanks

[Sidewinder]

It appears your MDAC is up-to-date, but the VB seems to never had service applied. Since MDAC also includes the JET driver, it would seem everything should be ok. I run VB6 SP6 on my other machine and have no problems accessing any database at any version level. Only difference is the other machine runs XP Pro.

The lack of service on the VB could be the culprit as perhaps the control you're using has been updated. Best advice would be to keep your software up to date.

You might also write a simple program to open a database (Access 2003), execute a sql select statement, and display the results. Keep it simple by not binding the database to a control and see if you have any problems.

Good luck.

[touchstone57]

I'm not sure how to go about updating my VB. Usually I get the average Windows updates but that does not apply to VB does it? There doesn't seem to be any options for updating in VB.

You might also write a simple program to open a database (Access 2003), execute a sql select statement, and display the results. Keep it simple by not binding the database to a control and see if you have any problems.

How would I go about doing this? I'm only an amateur so I don't know, I know how code works and how to apply it, I'm just not sure of the specific code to do this as Ive only connected it to a database through the properties.

Note: My Visual basic 6.0 and Microsoft Office 2003 (programs only) are free software that I get from Microsoft because I am a student at college...If that explains anything about the versions...

[steelegbr]

There is a service pack available for VB6 available >here<.

I can't remember what it updates but it may help with your problems.

[Sidewinder]

Actually the above download if for the VB6 runtime only. The full download can be found at VB SP6 and it is quite large.

You might also give this a read as this may apply: PRB Error

Your best bet is still to upgrade VB to SP6.

Good luck. 

[touchstone57]

Well I downloaded the Service pack 6 but I'm not sure where to extract the files to. I extracted the files to Microsoft Visual studio folder in then program files but it appeared to have no affect and I still got the error message.

[Sidewinder]

The instructions were on the same page as the download:

Instructions


    * Before starting the download, create a download directory on your computer. If your internet connection is less than 300K, it is recommended that you run the multi-part download by following the "More Information" link at the upper right, then clicking "Download Now."

    * Click "Download" to begin downloading the single download. When prompted by the download software, choose the option "Save this program to disk" and click OK. Then select the directory you created on your computer.

    * Run the file from the download directory. When prompted, select the same directory you created on your computer. You will be expanding the contents of the EXE into this directory.

    * Run SetupSP6.exe from the download directory. When you accept the terms of the electronic End User License Agreement (EULA) the setup software will replace the appropriate files in your Visual Basic 6.0 installation.

You also mention Visual Studio. You can upgrade the studio programs with the update located at Visual Studio SP6

I may have forgotten to mention that in your project you will need to create a reference to Microsoft ActiveX Data Objects 2.8 Library and Microsoft ActiveX Data Objects Recordset 2.8 Library. You do this from the Project drop-down menu.

Good luck.

[touchstone57]

Yes but the instruction where not fully specific. I ran the install and it does not install the files for you, it asks you to choose a directory with the files in, in which to extract to, so I extracted them to the Visual studio folder in Program files.

But Visual Basic 6.0 is part of the Visual studios package...

Could you tell me the folder I should extract the files too? it should be program files right?

[Sidewinder]

After the download, run the file just downloaded: VB6SP6B. This unpack the file into a set of component files. (You can use the same directory you used for the download)

Then run the setupSP6 application file to do the actual update. After you accept the EULA, the update will run.

When complete, open up VB and check Help==>About which should now mention that SP6 was applied.

Hopefully this should fix your problem. If not please post back with the exact error message you get.

 

[touchstone57]

*censored* when I click setupSP6  I get an error message. I have made a picture of the error message.

Click here

[Sidewinder]

I've got to remember to ask the basic questions in the beginning, but what OS are you running? There are two setupsp6 files in the package. One has a lst extension, the other a exe extension. You need to double click the exe file from within Windows Explorer.

Do not try to run the setup file from the cmd or command environment. What update did you finally decide on, Visual Studio SP6 or Visual Basic SP6?