VB6 & Access 2000

[Nias]

What is the easiest way to access an Access 2000 database with VB6? All I am getting is an unreconized database format error. Is there some data.ocx update I can download or do I have to jump through hoops first?

[Clunietp]

lately, I've answered this same question every 2 or 3 days ......
http://support.microsoft.com/support...G=ENG&SA=ALLKB

Tom

[David Laplante]

Hi Nias
I have tried several things to do the very same and unfortunatly there is no control upgrade or anything.... you have to write code for everything..... but I found that if you make a good function once... it deals for all situations...... reply if you wnat an example.

[Nias]

If you have sample code that would be terrific. That is what I am looking for. I thought for sure I could download an OCX for it from MSDN but no luck. Thanks everyone for the quick response. I only found this site today and I found it very resourceful!

[Clunietp]

Source code is located on the link I posted. If I were to post code, it would be the exact same as what's on that page...

[death]

You could just get access 2000 to save the database in a access 97 format.
Or use the ADO control

[gravyboy]

Clunietp has more than likely already covered this but here goes.


1. Use code. This can be one of two ways DAO (Data Access Objects) or ADO (ActiveX Data Objects).

2. Use a data access control. Again this can be DAO or ADO.
---------------
ADO code will look like

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

Dim sCnn As String

sCnn = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source= full path to your db;"


'This opens the connection to the database spedcified in the string.
cnn.Open sCnn

'Opens the recordset. The sting is the sort of string your Access queries generate.
rst.Open "SELECT * FROM atable", cnn, adOpenKeyset, adLockOptimistic


'This just scrolls through the records and updates the immediates window with a value.
With rst
   .MoveFirst
   Do While Not rst.EOF
      Debug.Print ![afieldname]
      Debug.Print
   .MoveNext
   Loop
End With

End Sub

Apologies the previous code was crappy and had loads of typos....

[This message has been edited by gravyboy (edited 01-12-2000).]

[Nias]

Ok,
Using the code found at: http://support.microsoft.com/support...G=ENG&SA=ALLKB as described above (I did find it very useful) how do I modify it to open a table using a query. I know I am pretty close but something isn't quite working right. I tried the code example pasted here but it errored out on the sCnn string. I did have success opening the table and viewing the data but I want to apply a query with an order by clause without resorting the table.

[Nias]

DOH!
I think I figured it out. It seems I had the coding correct to begin with, but my SQL query was comparing date fields and if I use 1/18/00 (for instance) in my query I get a divide by zero error. If I put the dates in different formats yyyy/mm/dd or yy/mm/dd (for instance) I get all data back (date doesn't work). If I put in 1/18/99 it works great (as long as I want last years stuff).
For now I will put the query in Access and move on but if anybody has a resolution on this one let me know please.

Nias