Database Gurus only (Need help ASAP)

[Doc Scheinder]

Hello,

I'm trying to connect an access database to VB, but everytime I try to set the record source on the data object, I get an error telling me that the database is of an unknown format or that the format of the datbase is unrecognized. Any suggestions? Sample code will also be helpful, maybe it's something in the code.

[jmcswain]

Yep, that happens if you try to connect to Access 2000 using DAO 3.51. You need to use DAO 3.6 instead, but there is an issue with that. The package and deployment wizard won't install the necessary DAO libraries for 3.6.

Aside from that, you should use ADO instead of DAO anyway.

[shanzek]

Here's some sample code for a project I was toying with. It shows opening the access database, doing both a query and a seek in a recordset. Hope it answers more questions than it creates...

' create a form with 6 controls: list1, text1, text2, text3, text4 and text5

' this is in general declarations...

Public db1 As Database, rs1 As Recordset, qry1 As String, rs2 As Recordset
Const dbname = "\commissions\zcs.mdb"



Private Sub Form_Load()
List1.Clear ' clear out a list box to load information into...
x = Space(90)

Set db1 = OpenDatabase(dbname) ' open the database
qry1 = "Select * From PcLoc Order By location_number" ' create a query; PcLoc is a table

' the above query will find all records in the PcLoc table and sort them by the field location_number


Set rs1 = db1.OpenRecordset(qry1, dbOpenDynaset) ' this executes the query
Set rs2 = db1.OpenRecordset("PcLoc", dbOpenTable) ' this opens another recordset to read in detail
' pcloc has 5 fields
' location_number <- primary key...
' customer_number
' customer_name
' location_name
' route_number


rs2.Index = "PrimaryKey" ' want to use the primary key to access this recordset

If rs1.BOF Then GoTo no_records ' if no records - skip the loop...
rs1.MoveFirst ' goto the first record in the selected list
Do Until rs1.EOF ' keep looping until end of file...


Mid(x, 1, 4) = rs1![location_number] ' i'm building a string to stick in the list box
Mid(x, 7, 4) = rs1![customer_number]
If IsNull(rs1![customer_name]) Then
Mid(x, 13, 30) = ""
Else
Mid(x, 13, 30) = rs1![customer_name]
End If
If IsNull(rs1![location_name]) Then
Mid(x, 45, 30) = ""
Else
Mid(x, 45, 30) = rs1![location_name]
End If

List1.AddItem x
x = Space(90)
rs1.MoveNext ' go get another record if any...

Loop



no_records:

'rs1.close ' close the recordset
'db1.close ' close the database

End Sub




Private Sub List1_Click()
' if you click on a line in the list box, this will look for the matching record in the rs2 recordset...

rs2.Seek "=", Mid(List1.Text, 1, 4)
'Stop
Text1.Text = rs2![location_number]
Text2.Text = rs2![customer_number]
If IsNull(rs2![customer_name]) Then
Text3.Text = ""
Else
Text3.Text = rs2![customer_name]
End If
If IsNull(rs2![location_name]) Then
Text4.Text = ""
Else
Text4.Text = rs2![location_name]
End If
If IsNull(rs2![route_number]) Then
Text5.Text = ""
Else
Text5.Text = rs2![route_number]
End If

End Sub

************
steve hanzek
vb6 sp3
[email protected]

[Doc Scheinder]

Thanks guys it looks good, I'll try these and let you know. I did not know that about Access 2000, Good looking out.