VB6 and Access Tables/Data

[PALman]

I am setting up new forms in VB6 which using the VB Data Form Wizard allow me to connect to my tables in MS Access. I am now at a stage where some forms display the first record of the Access Table and I am able to page down/up through the records. However, although I can change the data there is no way to save the changes. Any thoughts ? Also with a few tables I get the following error when I open it's form... "Data error event hit err... No value given for one or more required parameters". When this happens no records or data is shown allthough form and all the tables fields are visible. I presume there is something in the access table that is causing this but not sure what.
Once again, any help on either of these two problems is much appreciated.

 

[dedo8816]

You should be able to get all this from the wizard, when you create the forms through the wizard it gives you the options Add, Delete, Update, Close etc... When you click Add it should allow you to enter new information and auto save it.
Im new to the whole thing myself but i made one and mine works..

 

[Andrzejek]

A few years ago I created a little help of how to connect to (any) database thru ADODB. Here it is, I hope it will help you.

To create the connection to your database we will use Data Environment:

Start Visual Basic 6.0
In New tab choose Data Project
In Project-Data Project view double click on DataEnvironment1 (DataEnvironment1)
That will open DataProject - DataEnvironment1 (DataEnvironment1) with
DataEnvironment1
Connection1

Right-Click on Connection1 and choose Properties…
In Provider tab choose Microsotf OLE DB Provider for ODBC Drivers
Click Next >> command button, you are in Connection tab
Choose option Use connection string (we will use this string later)
Click on Build… command button
In File Data Source tab choose MS Access 97 Database (not sharable).dsn
(or whatever it is you use to connect to your database)
Click on OK button
On Login form click OK button
Choose the path to your Database and click OK
Click on Test Connection command button,
you should have "Test connection succeeded" message Click OK

Copy the connection string you just build, it starts with something like;
"DSN=MS Access 97 Database;DBQ=C:\…….." and paste it to Notepad or somewhere.

Right-Click on Connection1, choose "Add Command"
That will add Command1 to your view, right-click on Command1
Choose Properties
In General tab, click on SQL Statement option button, then choose SQL Builder command button
That will open Design: Command1 window and Data View panel.with
Data Links
Data Environment Connections
(+) Connection1

Double-Click on (+)Connection1, that will show you
(+) Tables
(+) Views

Where you can see all your tables and views (if you have any).

I use this tool to build all my SQL's and to check existing ones, too.
To create any SQL here, just drag any table to the upper part where your tool tip say "Drag and drop tables and columns here"

Save it and re-use it often.

To create the VB 6.0 Project connecting to your database with ADODB:

Open VB 6.0
Choose Standard.EXE
In Project - References… check Microsoft ActiveX Data Objects 2.x library
(you may have other version, like 2.0)

I have my Connection and Closing to database done in the Module.

Option Explicit
Public Cn As ADODB.Connection

Public Sub OpenConnection()
Dim strCn As String

'Here goes your connection string we (you) build
'Paste it from Notepad or wherever you have it
strCn = "DSN=MS Access 97 Database;" _
        & "DBQ=C:\…"

Set Cn = New ADODB.Connection
Cn.ConnectionString = strCn
Cn.CursorLocation = adUseNone
Cn.Open

'At this place you have connection to your Database

End Sub

Public Sub ExitAll()
'I close it in the Module this way because I can call it
'from any place in my program and close it properly
    Cn.Close
    End
End Sub

And then in my Forms I just create recordsets and play with them.
I have just ONE Connection to my database, but many recordsets.
Remember: connections are expensive, recordsets are cheap.


On my Form I have :
cboClass combo box, style = DropDown List
MSFlexGrid and cmdExit command button

'This Form populates Combo box (style = DropDown list)
'and the choice from combo box populates MSFlexGrid

Option Explicit
Dim recClass As ADODB.Recordset
Dim recInfo As ADODB.Recordset
Dim intCount As Integer

Private Sub Form_Load()
Dim strClassSql As String

Call OpenConnection	'from Module

'If I am going to use the same recordset many times
'I like to 'have it ready' in Form_Load like this:

Set recInfo = New ADODB.Recordset
recInfo.CursorType = adOpenStatic
recInfo.CursorLocation = adUseClient
recInfo.LockType = adLockReadOnly

'If it is one time use, I do it all-in-one place

strClassSql = "SELECT ClassName From Classes " _
& " ORDER BY ClassName"

Set recClass = New ADODB.Recordset
recClass.CursorType = adOpenStatic
recClass.CursorLocation = adUseClient
recClass.LockType = adLockReadOnly
recClass.Open strClassSql, Cn

'This recordset is ready to play with

cboClass.Clear
For intCount = 1 To recClass.RecordCount
    cboClass.AddItem (recClass!ClassName)
    recClass.MoveNext
Next intCount

cboClass.Text = cboClass.List(0)

recClass.Close

End Sub


Private Sub cboClass_Click()
Dim strSQL As String

strSQL = "SELECT BookInfo.* FROM BookInfo " _
        & " WHERE (Class = '" & cboClass.Text & "')"

'choice from combo box may be recalled this way

recInfo.Open strSQL, Cn

'recInfo is ready to play with, you get the idea…

With MSFlexGrid1
    .Clear
    .Rows = 2
    .Cols = 4
    .FormatString = "One" & vbTab & "Two" & _
vbTab & "Three" & vbTab & "Four"
    For intCount = 1 To recInfo.RecordCount
        .AddItem (recInfo!Title) & vbTab & _
(recInfo!Author) & vbTab & (recInfo!Price) & _
vbTab & (recInfo!Name)

'(recInfo!Column1) is a shortcut, the full version is:
'recInfo.Fields("Column1").Value, 
'but I don't like- too long

        recInfo.MoveNext
    Next intCount
    .ColWidth(0) = .Width / 4.1
    .ColWidth(1) = .Width / 4.1
    .ColWidth(2) = .Width / 4.1
    .ColWidth(3) = .Width / 4.1
    .RowHeight(1) = 40
End With

recInfo.Close

End Sub

Private Sub cmdExit_Click()
    Call ExitAll		'from Module
    End
End Sub



Private Sub cmdUpdate_Click()
	Cn,Execute "Update MyTable Set Col = 'Steve'"
End Sub

Private Sub cmdInsert_Click()
	Cn,Execute "Insert Into MyTable …"
End Sub

Private Sub cmdDelete_Click()
	Cn,Execute "Delete from MyTable …"
End Sub

Private Sub Form_Unload(Cancel As Integer)
'It is a good idea to do that.
'If not, you may run out of cursors in your database
    Set recClass = Nothing
    Set recInfo = Nothing
End Sub

Hope this helps.

---- Andy

 

[PALman]

dedo8816,
I tried the Data Form Wizard again but I cannot find the Add option you mentioned. I select all the options Add, Delete, Update, Close etc but the Show Data Control is greyed out so I cannot select this. So I am still not able to click Add to allow me to enter new information and auto save it.
Thanks

 

[PALman]

Hi Andy,
Thanks for your help. I, being new to VB6 will need some time to get to grips with what you've sent me, however I will follow your instructions and get back to you. Your programming looks just what I need to help me develop my own project. Many thanks,

 

[PALman]

I have been making steady progress creating new forms in VB6 using the Data Form Wizard to connect to my tables in Access 2000. I have been able to connect/read/write to three tables without any problem. However there are two tables in the SAME database (QC4.mdb) which I cannot connect with. When I try to open these I get same error as reported at the top of this post... "Data error event hit err:No value given for one or more required parameters".
Any help would be much appreciated.

 

[BobRodes]

Andy's form looks like a good candidate for a Template. To create a template, save the project in C:\Program Files\Microsoft Visual Studio\VB98\Template\Projects, assuming that you install VB in the default location.

Bob

 

[PALman]

Thanks Bob,
I shall try that, however what I cannot understand is why I can connect to three tables and not two. They are all in the same database and as far as I can see all connection strings are the same and recordsources are showing the correct tables and fields, but still I get the error "No value given for one or more required parameters". I shall try using Andy's form as a template this afternoon but I fear the suggestions for what is causing error are drying up. I have tried searching for this error here and found little in the way of answers. So as usual any help is very much appreciated.

 

[BobRodes]

When you get that error, it's because you are calling a stored procedure and not passing it a parameter it requires. It's analogous to calling a procedure with the wrong number of arguments. I'm not sure why you're getting the error, but this might help you.

If you're using the Data Form Wizard, make sure you select the "code" option rather than the "data control" option. Then study the code and explore removing the "noise" or unnecessary code.

HTH

Bob

 

[PALman]

Hi Andy,
I have been trying your coding but me being a newie I wonder if you could tell me what I am doing wrong. I have listed part of your coding here the last line of which gives the error...[ODBC Microsoft Access Driver] Too few parameters... Expected 3

Set recInfo = New ADODB.Recordset
recInfo.CursorType = adOpenStatic
recInfo.CursorLocation = adUseClient
recInfo.LockType = adLockReadOnly

'If it is one time use, I do it all-in-one place

'strClassSql = "SELECT ClassName From Classes " _ & " ORDER BY ClassName"

strClassSql = "SELECT M-CertNo, OrderNo from [Booking In Log] " & " Order by M-CertNo"

Set recClass = New ADODB.Recordset
recClass.CursorType = adOpenStatic
recClass.CursorLocation = adUseClient
recClass.LockType = adLockReadOnly
recClass.Open strClassSql, Cn <<<<< Program stops here.

I have edited strClassSql = to select fields from my Booking In Log Table. Perhaps this is the problem or could it be in the connection which I pasted as per your instructions. It reads...
DBQ=C:\000-Quality Control Program\QC4.mdb;DefaultDir=C:\000-Quality Control Program;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\MyConnection-01.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

Once again thanks for the code/program and apologies for not being able to get it to run. Any further help would be very much appreciated. thanks,

 

[gmmastros]

Try...

strClassSql = "SELECT [!][[/!]M-CertNo[!]][/!], OrderNo from [Booking In Log] " & " Order by [!][[/!]M-CertNo[!]][/!]"

The square brackets are necessary around your table name because it contains spaces. They are also necessary around your field name because of the dash. Access is probably interpretting the query as "A field named M subtract a field named CertNo".

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

Aha. I bet George is right.

 

[PALman]

Hi gmmastros and Bob
I tried...
strClassSql = "SELECT [M-CertNo], OrderNo from [Booking In Log] " & " Order by [M-CertNo]"
but I get same error... Too few parameters... Expected 1 where the number of parameters has went down from 3 to 1.

 

[gmmastros]

Try square brackets around [!][[/!]OrderNo[!]][/!] also.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Andrzejek]

Try:

Debug.Print strClassSql

and see if your sql makes sense in Debug window.

What I do, I create my SQLs in Data Environment and then copy it into my VB app.

Or, cut SQL from Debug window and dump it into Data Environment to see if it works the way I want.

HTH

--- Andy

 

[PALman]

Bob,
I have also tried your earlier suggestion to use the "code" option rather than the "data control" option within the Data Form Wizard but this gives error..."Object does not source automation events" on the first line...
Dim WithEvents adoPrimaryRS As Recordset.
I also noticed there is no connection to be found in the record navigation control box.
I am still trying to find a solution.
Thanks again.

 

[BobRodes]

Well, if the data control option works, go ahead and use it. I suggested the code option so you could look at the code and see how other people do the job. On the other hand, too, you might want to more thoroughly investigate Andy's code post, for the same reason. Let's boil his code down a bit. To create a basic ADO connection:

dim cn as adodb.connection
dim rs as adodb.recordset
set cn = new adodb.connection'
set rs = new adodb.recordset
cn.open "my connection string, which I will have to find"
with rs
    .open "any valid SQL select statement", cn
    do until .eof
        debug.print rs!anyvalidfield
    loop
end with

That will print some of your data to the debug window, and might get you started on the right path. You should be able to find a valid connection string by evaluating the connectinstring property of your ADO data control.

HTH

Bob

 

[PALman]

Bob,
The... Set cn and Set rs lines gives error "Invalid outside procedure". I have probably done something wrong here, but its late here and will have another try tomorrow.
Thanks again

 

[PALman]

Hi Andy and Bob,
I have finally established connection to one of my tables [Booking In Log] where I couldn't earlier. I now see the field values for the field JobNo for this table when I click on the combobox pulldown list. But when I click on any of these I get the same error as before... "Too few parameters... Expected 1". I have listed here some lines of code that I think could be the problem...

'strClassSql = "SELECT ClassName From Classes " & " ORDER BY ClassName" <<< Now replaced with next line
strClassSql = "SELECT [M-CertNo], [JobNo] from [Booking In Log] " & " Order by [M-CertNo]"

Debug.Print strClassSql <<< this line gives the following in the Immediate Window...
SELECT [M-CertNo], [JobNo] from [Booking In Log] Order by [M-CertNo]

cboClass.Clear
For intCount = 1 To recClass.RecordCount
' cboClass.AddItem (recClass!ClassName)<<< This replaced with next line
cboClass.AddItem (recClass![JobNo])
recClass.MoveNext
Next intCount
cboClass.Text = cboClass.List(0)
recClass.Close

Dim MSFlexGrid1 As MSHFlexGrid <<< THIS IS MY ADDITIONAL LINE added after MSFlexGrid1 not Dimensioned error

'strSQL = "SELECT BookInfo.* FROM BookInfo " & " WHERE (Class = '" & cboClass.Text & "')" <<< This replaced with next line
strSQL = "SELECT JobNo FROM [Booking In Log] " & " WHERE (Class = '" & cboClass.Text & "')"

'choice from combo box may be recalled this way

recInfpen strSQL, Cn

As far as I can see fieldnames have been spelt correctly in the two instances of SELECT.
Can I ask what the end result of selecting from the combobox should look like. I have never used MHFlexGrid before.
Thanks once again for all your help. I think we are almost there.

 

[Andrzejek]

First, you do not need to Dim MSFlexGrid1,
It is another control on your Form, like a label or a text box or a combo box. So:
Dim MSFlexGrid1 As MSHFlexGrid
may couse an error. (I don't know that for sure)

Line:
cboClass.Text = cboClass.List(0)
will fire click event of a cboClass, and that's where I have logic to retreive any data based on the user's choice from combo box.

Be carefull - if your combo box is set to DropDownList (it is what I use) and you Double_click on it to get to its code, the _Change event is the default one, but combo box as list does NOT have Change event, just choose CLick event and you will be fine.

As fas as database goes, I would avoid table names and field names with spaces. Use '_' instead.

HTH

---- Andy