Help requested

[AlexanderBB]

Hello,

I'm a total newbie to this and floundering around quite badly with online tutorials.

I'm using VB6 and want to use that as the frontend for my Access table
and display select queries results.

So far I've managed to open my Access Recordset in VB and also to link a data control to a query. With the latter, as I click on the navigation buttons I see the items appearing in a label. But I cannot get them into a combo box list. Nor can I find the values into the debug window - and from there perhaps run through an AddItem loop, if that is how it's done?

Have also tried setting the combo box style set to 0, the DataSource to the datacontrol and the DataField to the Field name, but there's no dropdown list so I must have missed something.

Any help appreciated.

[si_the_geek]

Welcome to VBForums

Rather than using a data control (see the article Why is using bound controls a bad thing? ) , like most people I would recommend using ADO code instead.

There are several articles in the "Classic VB - ADO" section of our Database Development FAQs/Tutorials (at the top of this forum) which show how to use it, including the "ADO Tutorial" which covers the basics, and one which shows how to fill a combo.

[AlexanderBB]

Thanks Si,

Will take a look... but I've never found one yet I could follow!
Full of jardon - ADO, DAO - all Egyptian to me Bound is similarly weird!
It's a struggle - but here goes...

Cheers

[AlexanderBB]

Si, your link to ADO Tutorial just brings up an ad for Visual studio.

I did get my combo filled by code and somehow fluked a way to open a recordset so back on track, to an extent. But very curious - with this Data control - if it's associated with a label, you see the data. But what if you want to debug.print it?

OK on not using Data control as per your first link, and I wont, but still bugged I can't see anything except in a label.

[Wen Lie]

i don't quite get what your meaning at your post.
but I think, rather than you use a data control, it's better you use ADO code. it's better and more flexible than the data control.

or can you post your code, for a better understanding on which part goes wrong ?


Regards,
Willy

[si_the_geek]

The link you clicked was an Ad-word I'm afraid, I didn't put a link there (but there is a direct link in my signature, or via the FAQs link I gave).

In terms of the jargon:
bound = using a control (such as "Data1") to do the database work.
dao/ado = the two main technologies to work with a database (either can be bound or pure code). DAO is extremely out of date (since 1997) but ADO is still current, so ADO is almost certainly the best way to go.

If you are using code to interact with the database (as per the tutorial), you can use something like this for Debug.Print:

Debug.Print rs.Fields("fieldname").Value

[AlexanderBB]

Hi Guys, thanks for the responces.
Wen, I have the required result in Access, but wanted to use VB to have a standalone .exe I could give to someone else. My next big hurdle will be to see if the table can also be included and compiled with the .exe.

Si, I didn't get the tutorial running, but did find something else explaining how to use the Data Control and I could connect (bind?) that to a label and see the data using the arrows to move around. But I could not 'get' the data into a variable, to then use .additem and populate the combo box.
No matter, I changed plans to

Option Explicit
Public gobjEmpDB As Database
Private Rst As Recordset

Public Sub OpenEmpDatabase()
Set gobjEmpDB = OpenDatabase("E:\Access\SheetMusic\Sheetmusic.mdb")
End Sub

Public Sub CloseEmpDatabase()
gobjEmpDB.Close
Set gobjEmpDB = Nothing
End Sub

Public Sub CenterForm(pobjForm As Form)
 With pobjForm
.Top = (Screen.Height - .Height) / 2
.Left = (Screen.Width - .Width) / 2
End With
End Sub

Sub MyStart()
Dim First As Boolean, tmp
OpenEmpDatabase
Set Rst = gobjEmpDB.OpenRecordset("qryLNames")
Do
If IsNull(Rst(0)) = False Then
Form1.cboChooseLabel.AddItem Rst(0)
End If

The strange names, 'gobjEmpDB' etc. are those used in the tutorial I found.
The above works. Is that ADO ??
I Googled and discovered
DAO = Database Access Object
ADO = ActiveX Data Objects

Doesn't mean much to me. In Access I use
Dim db As DAO.Database
Set db = CurrentDb
Dim r As DAO.Recordset

because I was told to. Prior to that it worked without the DAO prefix. Maybe ADO would work too? Any advantages aren't obvious.

Regards, ABB

[si_the_geek]

...wanted to use VB to have a standalone .exe I could give to someone else. My next big hurdle will be to see if the table can also be included and compiled with the .exe.

A stand-alone VB executable is not possible, whatever you do you will need some extra files (but you might get lucky, and find that the user has them already). There is more info in the article Why doesn't my program work on another computer? from our Classic VB FAQs

What you should do is create some sort of installer for your program, which can also contain your database (trying to put the database into the actual executable will waste lots of time, and totally fail in most situations).

Si, I didn't get the tutorial running, but did find something else explaining how to use the Data Control and I could connect (bind?) that to a label and see the data using the arrows to move around.

That's impressive - you've managed to find a tutorial from 1997 (when VB5 came out), which was out of date in 1998 (when VB6 came out). Either that, or it was written by somebody who has been giving bad advice on what to use.

If you look at the help for the (DAO) data control, you will see that it explicitly says not to use it - it was only included for backwards compatibility.

But I could not 'get' the data into a variable, to then use .additem and populate the combo box.

That is one of the joys of binding.

No matter, I changed plans to ...

That is better, because you are using code... but it is still DAO.

Doesn't mean much to me. In Access I use
Dim db As DAO.Database
Set db = CurrentDb
Dim r As DAO.Recordset

because I was told to. Prior to that it worked without the DAO prefix.

The prefix is important because it without it you can get bugs and errors - because VB doesn't know which kind of Recordset you intended (DAO / ADO / etc), and if it gives you the wrong one at some point then there will be problems with the other code that uses it.

Maybe ADO would work too?

It would work.

Any advantages aren't obvious.

DAO has been seriously out of date for many years (there have been no updates etc for about 10 years), and it fails in many situations - such as with 64-bit versions of Windows.

If you like having 'random' errors that cannot be solved, stick with DAO.

If you would prefer to use technology that is still current (and still being supported), use ADO. There are various extra features, but nothing dramatic in terms of the basics for a single-user database (just things that save you development time for things like copying data to an array).

[AlexanderBB]

Si,
Thanks for the comprehensive reply and steering me clear of trying to include the database with the exe. Guess I've just been lucky so far with getting things working on other computers but will check thoroughly out the installer and what it entails.
OK on not using the DataControl. (Yes, I read the advice but wanted to try it out anyway).

>That is better, because you are using code... but it is still DAO.

How it is changed to ADO, and for that matter, where is the DAO setting?

I imagine one of these three lines - but none mention either.

Public gobjEmpDB As Database
Set gobjEmpDB = OpenDatabase(My mdb)
Set Rst = gobjEmpDB.OpenRecordset(Query or table in my mdb)

Is the DAO setting done in Access or VB ? In Access I changed

Dim db As DAO.Database
to
Dim db As ADO.Database

But that just invokes User-defined type not defined.

Regards, ABB

[si_the_geek]

I was under the impression you were writing code in VB6, rather than Access.

In VB6 you follow the ADO Tutorial - which shows what code to use, and explains what everything does.

In Access you follow the ADO Tutorial, but there are minor differences for "irrelevant" things (such as the syntax to change the mouse cursor, and set control values). The References screen is in a different place (via the Tools menu?), but I think that for Access 2000 and later the Reference for ADO is already set.

[AlexanderBB]

I was under the impression you were writing code in VB6, rather than Access.

That's correct, I was using the Access syntax as an example.

In VB6 you follow the ADO Tutorial - which shows what code to use, and explains what everything does.

I've finished it now - and it's working satisfactoraly.
Thanks for the pointers