Hallo,

I wanted to ask what code is required to create new records in a linked SQL table using Visual Basic. I can retreive specific data and append it but cant create new records. here is what i have at the moment:


Private Sub cmdNewReseller_Click()

Dim ws As Workspace
Dim db,curDB As Database
Dim rs_form As DAO.Recordset
Dim strConnection As String

Set ws = DBEngine.Workspaces(0)

Let strConnection = "ODBC;DSN=MagicHDIQ" & ";UID=" & "magic" _
    & ";PWD=" & "magic"

Set db = ws.OpenDatabase("", False, False, strConnection)

Set curDB = CurrentDb
Set rs_form = curDB.OpenRecordset("_SMDBA_._COMPANY_") 'this one gives error msg that table cant be found.
another alternative I tried was... (below)
Set rs_form = db.OpenRecordset("_SMDBA_._COMPANY_") 'this but get an error msg "ODBC--Call failed" at the line 'rs_form.Update' below.
rs_form.AddNew
rs_form!CODE = "test"
rs_form.Update

End Sub



Im assuming that the 'rs_form.AddNew' line has been successfully executed, but pops an error maybe because not all fields are filled in in the new record? I just populated that particular field to test if I could create a new record.

Im sorry I forgot to say thank you to anyone who may shed some light on this. I hit the post button instead of preview.

anyway...

Any help or advice will be greatly appreciated, thanks for your time and help.

Derek

I am not an expert by any stretch of the imagination, but the first question I would ask is... Does the table _SMDBA_._COMPANY_ currently exist in the database?

Mike
EchoVue.com

Hi echovue,

the _SMDBA_._COMPANY_ table is a linked table from an external sql database. I currently have my fields from that table in my forms and are updated. I can add new records if I open the table directly from the database window. But I need to to be able to do this in code from the FE forms. Thanks very much.