Problem with Access 2000 and VB 6

[korgul]

I am trying to create a VB program that will access a MS office 2000 Access database. I keep getting the error that the database is unrecognized.

This brings me to my question, Is VB 6 and Office 2000 compatible or am I doing something wrong? I know that the database is ok since I have gone into it since getting the error.


korgul

[JimG]

Ok, how are you going about doing this?
I started VB 6, created a new program (exe) using the Wizard and when it asked if I wanted to create a form based on tables/queries, I said yes. Running that part of the wizard, I attempted to load one of my database projects. At that point I got this error: "Unrecognizable database format E:\xxx\DummyTest.mdb" Is this the same error you're getting?
I think the problem could be ODBC drivers. I thought Access 2000 was comptabile with VB 6, but maybe not 100%. Going to play with the ODBC parts now.

edit - You are not alone in this: http://www.techimo.com/forum/t8091.html

[korgul]

Yep that is the same error.

I thought I saw something before but could not find it. Thanks for the link.

I was creating an .exe from scratch and adding the database with the data access. You will have to excuse me on the terminolgy i am new to VB.
I had a class on it when I was in school but that was lmost 2 years ago.

korgul

[JimG]

That's ok, I'm self taught in VB and VBA. My VB knowledge is limited with Access. I usually code with VB for HMI (human machine interface) applications. I'm much better in VBA with Access.

[JimG]

Korgul,

Check out that other thread: http://www.techimo.com/forum/t8091.html
I got it to work by converting the db back to Access 97, then make sure the Active X 2.0 data thing (sorry, can't remember the exact name) is checked. Got past that error with this. Good luck!

[vass0922]

Microsoft ActiveX Data Objects 2.0 ?


yeah if you're using VB with Access, ADO is the best way to do it. Dont bother with RDO and DAO they are outdated and probably wont be supported much longer.

Fairly easy as well, hardest part is setting up the connection junk.

the ADO control on a form is easy to do, but not good if you want things to be a little more dynamic (typically this is my case)

[JimG]

vass,
Yeah, you're right, that's it ... I had all those words floating in my head, but they were not forming the correct name.
Thanks.

[Whir]

If you're really saavy and your database has a lot of tables and queries, you can create an ADO Data Environment. It cuts down the hard coding if you've got say, 40 queries and ten tables. The query builder in VB6 is pretty decent, at least as good as Access (heck, might be the same one). I used the ADO-DE for an app that took form data, put it in the database, then read the entire database out and created a formatted text file for the MUD I imp on. Not the best way to go for high end dbase stuff, but if you're using Access, you're not using high end dbase stuff anyway.

-Whir

[PonzSpyder]

The query builder in VB6 is pretty decent

Hmmm... never used it, I'll have to check it out

I used something like this for VB6 to Access 2000:

Public DataSource As Variant
Public CN As New ADODB.Connection
DataSource = "Data Source=C:\Temp\Database.mdb"
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& DataSource & ";Mode=Read;Persist Security Info=False"
CN.Open

I think thats what it was, not sure though since it was a while ago and I dont have the current version of the code with me ATM.

[vass0922]

ponz, thats how I do it as well

Much more flexible if you want to be able to easily switch back and forth between a test DB, and a production DB.

Another solution (sorry lol) is using a UDL file. (if you have VB installed) you can right click and create a UDL file, if its not available create a empty text file and change the extension to .UDL if the icon changes it means you have the right software installed. I can't remember how to get the properties out of it, but I could find out if need be. Its been awhile

But you can double click it, and set all the properties for the connection this way. It worked great for us at my old job... we had a multi tier app and if we wanted ALL the clients to change databases (for an upgrade or whatever) we would just change the UDL on teh server and they'd change databases ) When the app asked for a connection the connection class asked the UDL where to go!

I've never used the DE at all... I tend to either create the sql in embedded sql
ie.
sSQL = " SELECT Column1, Column2"
sSQL = SQL & " FROM TableName"
sSQL = SQL & " WHERE Column1 = " & SomeIntegerVar
etc etc.

This way its not entirely hardcoded because as you concat the sql, you can throw in a variable