VBA to VB problems

[PALman]

I am finding a lot of problems trying to get my VB Access programming to run in VB6. For example I am looking for code which will do the same job as Dlookup and Me.JobNo.
In trying to use Recordset to access my Access 2000 database which has many tables forms and queries I have the following lines of code to start with...
Private Sub Command1_Click()
Dim DB As Database
Set DB = OpenDatabase("C:\000-Quality Control Program\QC4.mdb")
Dim RS As Recordset
Set RS = DB.OpenRecordset("Folder Paths", dbOpenSnapshot)
However I get the following error in Line 3 Set DB = ... Unrecognised database Format 'C:\000-Quality Control\QC4.mdb'.
Folder Paths is one of the many tables in my MS Access 2000 Database QC4.mdb
I have referenced MS DAO 3.51 and MS Access 9.0 Object Libraries but do not know what else to try.
Once again any help is much appreciated.

 

[jadams0173]

For VB6 and dao you need something like

Dim rsDAO As DAO.Recordset
Dim db As DAO.Database
Dim WS As DAO.Workspace
Dim T  As Single

Set WS = DBEngine.Workspaces(0)
'
Set db = WS.OpenDatabase(g_DBPath & g_DBName, False, False, "MS ACCESS;PWD=pw")
'
Set rsDAO = db.OpenRecordset("yourtable")

I tried to have patience but it took to long! -DW

 

[fredericofonseca]

Make sure you are referrencing the correct DAO engine. For AC2000 it needs to be 3.6, not 3.51.

Also when setting up the variables ALWAYS use full referencing to the object type, e.g. change
Dim DB As Database
to be
Dim DB As DAO.Database

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q238401

gives some explanation of the problem. Other links exist.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PALman]

Thanks Frederico,
I changed reference to 3.6 from 3.51.
Thanks jadams0173,
My coding now looks like this...
Private Sub Command1_Click()

Dim rsDAO As DAO.Recordset
Dim db As DAO.Database
Dim WS As DAO.Workspace
Dim T As Single

Set WS = DBEngine.Workspaces(0)

'Set db = WS.OpenDatabase(g_DBPath & g_DBName, False, False, "MS ACCESS;PWD=pw")
Set db = WS.OpenDatabase(("C:\000-Quality Control Program\QC4.mdb"), False, False, "MS ACCESS;PWD=pw")

Set rsDAO = db.OpenRecordset("Folder Paths", dbOpenSnapshot)

'New lines to list Fields of Folder Paths Table in Listbox1...
maxFields = rsFields.Count
For i = 1 To maxFields - 1
List1.AddItem RS.Fields(i).Name
Next

End Sub

It would appear that I am now opening the required db but program stops 4th line from the end... maxFields = rsFields.Count with error... Object Required.

 

[fredericofonseca]

rsfields is not defined.

change rsfields to
rs.fields

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Try

Dim maxFields As Integer
maxFields = rs[red]DAO.[/red]Fields.Count
For i = [red]0[/red] To maxFields - 1

Fields is a zero-based collection.

 

[PALman]

Thanks to all who offered help in this thread. I now have program running and now I am getting into the details of using Recordset.
Can I ask one other question?
Is the Control Wizard in VBA available in VB6?
That's where we draw a control box on a form and the wizard pops up and gives options and code for those options.
I can't find same in VB6.

 

[JoeAtWork]

VB6 does not have control wizards like Access VBA. You place the controls on the form, and then write the code yourself.