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.

 

[Andrzejek]

To have a MSHFlexGrid1 control available, go to

Project - Components....
Choose Microsoft Hierarchical Flex Grid Control

And you have it.

Also, if you want to, you can use the same recordset again.

cboClass.Clear
For intCount = 1 To recClass.RecordCount
     cboClass.AddItem (recClass![JobNo])
     recClass.MoveNext
Next intCount
[b]recClass.Close

If cboClass.ListCount > 0 Then
    cboClass.Text = cboClass.List(0)
End If
[/b]

Now you can re-use recClass somewhere else:

Private Sub cboClass_Click()

strSQL = "SELECT JobNo FROM [Booking In Log] " _
   & " WHERE (Class = '" & cboClass.Text & "')"

recClass.Open strSQL, Cn
    'Do your logic here
recClass.Close

End Sub

HTH

---- Andy

 

[PALman]

Thanks Andy (and Bob)
Andy... Your advise about table names and field names proved correct. I renamed the table "Booking In Log" to "BookingInLog" and after going back through your coding decided to start afresh and create a new form and connection with the Data Form Wizard and hey presto! it worked!
I find it strange that a table that I had no problem connecting to was named "Enquiry Desk". Now I am thinking of renaming it "EnquiryDesk" and recreating the connection and rebuild form.
I shall revisit your coding in the future because I was still getting errors, however in the meantime I need to press on with renaming all the tables and create all the forms for my project. I have lots to do.
Thanks guys for all your help, as I say I shall revist this method you have given me for setting up your programs.
No doubt too, I shall need help in other areas of my project as it progresses.
Thanks again

 

[Andrzejek]

Just one suggestion about renaming your tables/fields (you would find it the hard way anyway, but why waist time and frustrations…?)

Do not use reserved words, like TIME, DATE, SQL, NAME, SELECT, etc. If you need to have some of them, do TIME_OFF, FIRST_NAME, SELECT_QTY.

HTH

---- Andy

 

[PALman]

Thanks Andy,
I have already seen to that. For example Date01, Date02 etc.
I have been getting on well setting up all my forms and tables, however one table (I have inherited) has set me a problem. It has no fewer than 40 fields and when I try to bring these into my new form I get the error... "Out of memory". Any thoughts on how to overcome this without splitting up the table.
Thanks again,

 

[Andrzejek]

40 fields is not much, rather small table (in my opinion) unless you have a LOT rows of data and the table is huge this way. "Out of memory" error - you have not enough of it on your machine (unless you run a LOT of other applications at the same time.

Are you trying to retrieve ALL data from this table at once? If so, then why? You don’t need all data from this table, that’s why we have SQL to get only data we need.

If you need to contact me directly, I have an account on hotmail (a junkie one I get spam and such), but from there I can redirect you to my regular one I check on regular basis, my handle in andrzej7 so you can give it a try.

---- Andy

 

[BobRodes]

Hi Palman,

If you want to use my code, start a new VB project. Set a reference to Microsoft Activex Data Objects, latest version you see. Put the code in that project and run it. If that doesn't work, post back with the code and we'll take a look at it.

Bob