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?

 

[touchstone57]

I'm using Windows XP (SP2)

I have done all you said but to now avail, I extracted the files to the download folder and double clicked SetupSP6 and got the error message.

Click here to view error message

Or in text format
===================================
16 bit windows subsystem

NTVDM has encountered a System Error
Access is denied.
Choose 'Close' to terminate the application
===================================

[Sidewinder]

I have followed the instructions myself and am unable to recreate the message you're generating. There are only two exe files in the directory where the components were expanded (acmesetup.exe and setupsp6.exe). Double clicking on setupsp6 brings up a screen titled: "Visual Studio 6.0 Service Pack 6 Setup".

Suprisingly, I can  run this from the command prompt and/or the cmd prompt and/or Windows Explorer.

I can only suggest the downloaded file is corrupt or something else is going on here. I can think of no reason why the DOS VM got involved in this.

 

[touchstone57]

I'm not sure why this is. I have downloaded the file at least 3 times now and I get the same message...what does it mean 16 bit sub system? because I'm using 32 I'm sure...

It seems like one error turns into another

[Sidewinder]

Couldn't find much info on the net concerning the NTVDM problem in Windows XP. NTVDM is the 16-bit virtual machine inside Windows needed to run 16-bit programs. If you have legacy programs, it might be called into action, but many people will never even see it.

Try seeing if any entries in the event viewer give any hints as to what is going on:

Control Panel==>Administrative Tools==>Event Viewer

Check both the Application and the System logs. Maybe, just maybe they'll offer a hint of the problem. I'm quite sure the SP6 installer is a 32-bit program.

This problem only gets stranger and stranger.

[touchstone57]

I'm not sure what it says in the event viewer but I have made some pictures.

For application view click here

For system view click here

Note: May require enlarging for better viewing

Please tell me if anymore information is required.

[Sidewinder]

There is nothing in the logs that point to anything. How long was the lag from when you ran setupsp6 and you viewed the logs? Try the setup again and as soon as it fails, check the logs.

I would think you'd get an entry in Application log for the MsiInstaller or maybe for the NTDVM itself. Any clue would be a godsend at this point.

[touchstone57]

That is the log files only a few seconds after I ran the setup...what can the first error mean? the about Nview 

[Sidewinder]

The Nview entry (or any other entry) can be double clicked for more information. In any case the error occurred on 08/01/2007 which is either January in the UK or next August in the US. 

I'm at a complete loss. Maybe someone will come along that can help. I'm still stumped about how his relates to the NTDVM.

Good luck.

Since you apparently have the entire Visual Studio suite, try updating the suite from the link in post #10.

[touchstone57]

well I tried running the Visual Studio service pack also but with the same results...

Now I'm completely stumped...and I need to get VB fixed so I can work on my assignment

thanks for all of your help anyway

[touchstone57]

BREAKING NEWS
   =============

Yes I have some good news  , I finally managed to get it to install service pack 6! I was just playing around with it almost having lost all hope, and I decided I would unpack the setup files to a different directory than the one my file was downloaded to...I sent it to My videos (just for fun) and it installed successfully.

Now we get back to the original task at hand, to connect my Vb application to the database....

Fingers crossed

Hope all goes well

[Sidewinder]

I'm glad you finally got the update installed. Still don't understand why the location of the expanded files would make a difference, but I'm not one to argue with success.

Let's hope this fixes the problem you first posted, way back when.

Good luck.

[touchstone57]

Just to report back, I changed the "connect to" database to Access 2000 and now my program is working perfectly with no errors.

Thank for all of your time and help 

[mhk_nzs]

somebody please give the command how to link VB to Access database....

[Sidewinder]

Please do not hijack other poster's threads. You have a better chance of getting an answer if you post your own thread.

If I remember correctly, VB6 and VB.Net have wizards to connect to a database. You can also connect a control to a database using the property page during forms design or you can do it the old fashioned way and do it directly in the code.