odbc error 3146 with rs.update

[sharonchapman7]

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

 

[dhookom]

I don't see any code that Dim's db or rs. There is no ORDER BY in the OpenRecordset so how do you reliably know which record is being edited? You also aren't checking to see if there are any records returned.

Duane
Hook'D on Access
MS Access MVP

 

[jsteph]

Sharon,
3146 is a generic error, so (while in break mode at the update line) go into debug window, and type:
?errors(0)

The 0 means the first error returned by the db server, which will usually be the root error, then typically errors(1) would be "ODBC call failed", which is of little value.
--Jim

 

[sharonchapman7]

Sorry jsteph, here is the complete code.

Public Sub Load_Files_Click()
Dim strSQL As String
Dim intCntr As Integer
Dim db As Database
Dim rs As Recordset
Dim FileStream

On Error GoTo Err_Prefix_Ctrl_Nbr

FileStream = Shell("C:\Development\GEOINT\GEOINT\bin\Release\GEOINT.EXE", 1)

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

Exit_Prefix_CTRL_NBR:
rs.Close
Set rs = Nothing
Exit Sub

Err_Prefix_Ctrl_Nbr:
If Err = 3146 Then
MsgBox "Error Number: " & Err & " - " & Err.Description
Resume Exit_Prefix_CTRL_NBR
End If

End Sub

 

[dhookom]

Minimally, I would use:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    ...
    If Not (rs.BOF AND rs.EOF) Then
       rs.Edit

Again, I would never rely on "LAST" to point to a specific record.

You have no comments regarding:

  FileStream = Shell("C:\Development\GEOINT\GEOINT\bin\Release\GEOINT.EXE", 1)

Duane
Hook'D on Access
MS Access MVP

 

[sharonchapman7]

this statement:
FileStream = Shell("C:\Development\GEOINT\GEOINT\bin\Release\GEOINT.EXE", 1)

Runs a C# program that loads a picture into the File_Stream table using fileStreaming.

My code after this statement is to connect the picture that was just loaded, to the current record in Motion_Imagery. the C# program and the following code runs when a button called Load on the Motion_Imagery form is clicked.

Sharon

 

[lameid]

Just a thought why not just load the filestream using C# rather than loading an empty record and trying to find it?


Additionally, Shell runs ASYNCHRONOUSLY so, likely it is not done running when you try to modify the record.

 

[sharonchapman7]

lameid,
I am using C# to select the picture and load it into the database table. However I'm very new to C#, extremely new and I don't know how to load these two fields to the table using C#:

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

Attached is my C# code.

Thank you for your help on this.

Sharon

 

[lameid]

Sharon,

I'm newer to C# than you, I have never used it. You should try a different forum for that.

Here is a link to a MS article I used recently to use a file stream to load data into a SQL table using an Access Front End... I expect this is similar for Access data.

http://support.microsoft.com/default.aspx?scid=kb;en-us;258038

I would also expect there would be posts or FAQ's about doing the whole thing in Access if you search this site.

 

[sharonchapman7]

thanks lameid,
I'm not having any problem loading the filestream into the SQL database. But the table, filestream_files, contains the filestream. I have data in two fields that I need inserted into the filestream_files table. the filestream_files table is loaded in a C# program. the data is coming from the motion_imagery table which calls the C# program.

Sharon

 

[lameid]

Sharon,

I was suggesting you could load the file stream using access and then turn around and add the other values at the same time...

Or fix the C# code to do it. In theory the C# code could be modified to take command line paramaters that you could add to the shell call.