Open and update a table in VB/Access2000 1

[hceonetman]

Pretty new to VB coding. I am trying to create a VB module within an Access2000 database to populate a record in a table. This record will then serve as the datasource for a report. The requirements of the report make this too complicated to do within queries. My plan is to open a table with one record and then change each field based on values in other tables and queries. First I took code out of a book to just open and display fields in the table, and this doesn't work. Following is the code:

Sub DisplayFields()
Dim Connection As ADODB.Connection
Dim Catalog As ADOX.Catalog
Dim RecordSet As ADODB.RecordSet
Dim Field As Field

Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Q:\Dev\TestPayins\Payins.mdb"
Set Catalog.ActiveConnection = Connection
RecordSet.Open "Tbl_Aud_Revenue_Rcpt", Catalog.ActiveConnection, adOpenKeyset
RecordSet.Fields.Refresh
For Each Field In RecordSet.Fields
Debug.Print Field.Name & ", "; Field.Type & ", " & Field.ActualSize
Next
RecordSet.Close
Set RecordSet = Nothing
Set Catalog = Nothing
Connection.Close
Set Connection = Nothing
End Sub


I don't know if this is the correct method of opening a table in the current database. I get the error:
"the database has been placed in a state by user admin on machine CPU04 that prevents it from being opened or locked".

No one else is using the MDB, and I am identified as user Admin on my PC.
Also, recommendations on a good book to get up to speed in VB for Access programming would be welcome.

Thanks,
HCEONETMAN
The less things change, the more they remain the same.

 

[nicsin]

Have you got the table opened in access when you execute this code?

 

[hceonetman]

Nicsin,
The Database is opened but not the table. That is, I'm not viewing or designing it at the time.

HCEONETMAN

 

[nicsin]

HCEONETMAN,

have you tried executing the code without running access?

 

[cmmrfrds]

If you want to work in the current database then use the current connection. The database is opened in exclusive mode because you are designing you cannot open another connection on an exclusive database.

Use a different name in this dim, since Connection is a reservered word.
Dim CN As New ADODB.Connection '- create an instance along with the declaration with the New keyword.

Set CN = CurrentProject.Connection
RecordSet.Open "Tbl_Aud_Revenue_Rcpt", CN, adOpenKeyset

For Each Field In RecordSet.Fields
Debug.Print Field.Name & ", "; Field.Type & ", " & Field.ActualSize
Next
RecordSet.Close
Set RecordSet = Nothing
' don't need the Catalog, since any recordset already has a fields collection as meta data that is part of the recordset.
'Set Catalog = Nothing
CN.Close
Set CN = Nothing

 

[hceonetman]

Thanks CMMRFRDS,
I'm now able to open the two tables I want to work with. The next step is to update in one table based on various criteria. I open a second table with:
RecSet2.Open "Tbl_Test", Cn, adOpenDynamic
which should open it for edit. I then attempt to update a record with:
RecSet2.MoveFirst
RecSet2("Pg_Num&quot.Value = RecSet("Pg_Num&quot
I get an error message that "the current recordset does not support updating. This may be a limitation of the provider or of the selected locktype."
First of all, does vb allow me to have two recordsets open at the same time? If so, why am I not able to perform the update?

Thanks again,
HCEONETMAN
I prefer HoHos to poptarts because they require less cooking.

 

[cmmrfrds]

You can have mulitple recordsets open at the same time - no problem.

I am assuming you are working in Access (not a true client/server database), where the processing is handled on the Client PC. For Access set everything client side.

Look up the syntax for these properties.
CursorLocation = adUseClient
CursorType = adOpenStatic
LockType = adLockOptimistic

The recordset is NOT updatable because the default Lock Type is Read Only, you will need to change as suggested.

 

[hceonetman]

Cmmrfrds,
Between your post and the Ms Knowledgebase I was able to get it to perform the field update. That wraps up the week in a good way. Have a star on me.

Thanks,
HCEONETMAN

 

[cmmrfrds]

Great. Thank you. I am off to start the weekend by taking my son to his hockey game, which I enjoy. The Peewee level hockey is fun to watch.