ODBC Error 3146 on rs.update

[sharon.chapman7]

Hi,
Using MS Access, I have a form that is based on a table called Motion_Imagery. In the VBA of this code I want to take two fields on the form and add the data in those two fields to the last record that was added to table Online_Books. When I run my code I get the error when it gets to rs.update, here is my code:


Set db = CurrentDb()
Set rs = db.OpenRecordset("dbo_Filestream_Files", dbOpenDynaset, dbSeeChanges)



rs.MoveLast

rs.Edit
rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
rs![CTRL_ID] = Me.CTRL_NBR
rs.Update

Can you tell me what the problem is? Is this possible to do?

Thanks,
Sharon

[pbaldy]

What is error 3146? I'd point out that the table in your code isn't either of the ones mentioned. I would not count on MoveLast to find the right record (though it might). It's also inefficient to open the whole table. I'd open the recordset on an SQL statement that returned the specific record I wanted to update.

[sharon.chapman7]

What is error 3146? I'd point out that the table in your code isn't either of the ones mentioned. I would not count on MoveLast to find the right record (though it might). It's also inefficient to open the whole table. I'd open the recordset on an SQL statement that returned the specific record I wanted to update.

Sorry, you are correct. Instead of Online_Books I should have said dbo_Filestream_File for table 2. I don't know what the odbc error 3146 means.

[June7]

Why do you need to save this data to another record? Why was record created without it? Assuming the last record in table is the record you want to edit is risky. Are there multiple users?

I can't get that code syntax to work. But these do:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM dbo_Filestream_File;", cn, adOpenDynamic, adLockPessimistic
rs.MoveLast
rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
rs![CTRL_ID] = Me.CTRL_NBR
rs.Update

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("dbo_Filestream_File", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.Edit
rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
rs![CTRL_ID] = Me.CTRL_NBR
rs.Update

Either explicitely declaring the DAO was key or leaving the parens off CurrentDb in the second procedure.

[sharon.chapman7]

Thank you. I will give this code a try.

Sharon

Why do you need to save this data to another record? Why was record created without it? Assuming the last record in table is the record you want to edit is risky. Are there multiple users?

I can't get that code syntax to work. But these do:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM dbo_Filestream_File;", cn, adOpenDynamic, adLockPessimistic
rs.MoveLast
rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
rs![CTRL_ID] = Me.CTRL_NBR
rs.Update

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("dbo_Filestream_File", dbOpenDynaset, dbSeeChanges)
rs.MoveLast
rs.Edit
rs![Prefix_CTRL_NBR] = Me.Prefix_CTRL_NBR
rs![CTRL_ID] = Me.CTRL_NBR
rs.Update

Either explicitely declaring the DAO was key or leaving the parens off CurrentDb in the second procedure.

[sharon.chapman7]

In the filestream_File table I have a picture stored that goes with a record in the table called Motion_Imagery. I need to have some way of matching these two records up for reports. The Primary key for the Filestream_file table is fID. The Primary key for the Motion_Imagery table is Prefix_CTRL_NBR and CTRL_ID. By putting the two fields Prefix_CTRL_NBR and CTRL_ID in the Filestream_File table, I am then able to select a record from the filestream_File table that connects to the Motion_Table record.

Sharon

[sharon.chapman7]

thank you all. I have decided to try a different approach.

Sharon