odbc error with mySQL

[braimi]

Hi,

I have linked the tables from mySQL server. I can view the tables but when I try to insert some new record using Access forms, I receive error that
INSERT ON LINKED TABLE "TABLENAME" FAILED.

Please help me out of this.

Thanks

 

[boblarson]

Does your table in mySQL have a primary key defined?

 

[braimi]

Yes it has a primary key and I have solved the issue. problem was that one of the field was set to required while there was no box to enter the value on the form for that field.

but I am having another problem now. that is printing with current record. The form is based on the table in mysql and works fine but the primary key box is updated only when you save the record using the form and then close the form and reopen. not like access that as you start entering the data on the form the primary key box is updated. can some one tell that how can we do this while using mysql db?

Please help.

 

[DCrake]

It sounds like the primary key in your SQL table is an autonumber, and unlike Access this is created AFTER you save the record, whereas Access creates the autonumber as soon as any field is populated in the new record. Do as search on @@Identity to determine the new primary key of the new record.

 

[braimi]

thanks for your prompt reply DCrake... would you mind to please tell me in little more details. because I am not an expert just a basic user of access..

Thanks

 

[DCrake]

Here is an example of how to use the @@Identity. In this eample I am creating a new record in a table but do not know what the new autonumber (primary key) is.

So after I perform the update I execute the comand to get it and store the number to a public variable that I can use a filter argument when doing something else with this new record

Function CreateSession(WhoAmi As String)
Dim iSql As String
Dim sSql As String

'Saves the current login record back to the server
Dim NextSessionId As Integer
Dim Tbl As New ADODB.Recordset

        Set Tbl = New ADODB.Recordset
            sSql = "Select * From TblLoginSessions Where fldOrganisation = '" & szOrgCode & "'"
            
        With Tbl
            .Open sSql, MasterDbConn, adOpenKeyset, adLockOptimistic, adCmdText
            .AddNew
            .Fields("fldOrganisation").Value = szOrgCode
            .Fields("fldUserName").Value = WhoAmi
            .Fields("fldComputerName").Value = StrComputerName
            .Fields("fldLoginEvent").Value = Now()
            .Update
            .Close
        End With
        Set Tbl = MasterDbConn.Execute("Select @@identity As XPropertyID")
        LngLoginKey = Tbl.Fields("XPropertyID").Value
        Set Tbl = Nothing

End Function

I know your vba knowledge is not great (as you have expressed) but I think you should be able to follow it. If not someone else may be able to offer a different solution for you.

 

[braimi]

I can understand the example you have given and really thank you for the nice support.
You are right that my vba knowledge is very poor but if you can just do some more help in this so I will be able to resolve the issue.

Can you please guide me that how can I use this function and where I will use it? I mean on the form or on event?

Thanks

 

[DCrake]

First question: Are you using a bound or unbound form?

 

[braimi]

I am using a bound form which is bound to the table in mysql where primary key field exists

 

[Banana]

I just want to check on something...

Why do you think you need the ID? If it's to create related child records, the linked subforms should continue to work just fine even with the "late" assignment of the autonumbering.

Or maybe you want to open another form after you save the record. If you want it to alway open another form no matter what, you would adapt David's nice function in the public module then in first form's After_Update Event, do something like this:

DoCmd.Open acForm, "FormName" WhereCondition:="MyID=" & GetIdentity()

(David's function is named CreateSession and does more than just get @@identity, so if this is what you need, you just need to trim so it only return the @@identity.)

HTH.

 

[DCrake]

Ok. My solution is aimed at unbound forms (as it is an extract from a VB project). What you could try is to use the DoCmd.SaveRecord on the exit of the last field on the form. or have a manual save button on the from that the user can click to force a save. Then the PK will be populated. you should them be able to retrieve the PK from the table.

 

[braimi]

Thanks Banana... I actually want the id to be returned for printing current records. because in query the table.field=[forms]![formname]![field]

and unless the field on the form is not updated, I can not print current record. All it shows me a blank report without any data on it.

 

[Banana]

It's been a while, but as David suggested, you should be able to save the record and thus get the ID available when you click whatever button that makes the report. Is it not the case?

 

[braimi]

Thanks a lot DCrake and banana.... I got the report working now and I solved it by telling Crake's last solution.

Thanks guys