Updating Combo box if Table is in SQL Backend

[dmacpherson via AccessMonster.com]

I don't know if this is the correct forum to ask this Q.
Win 2000, Access 2003 SQL Server 2000


I want to update the Vendor tbl with a new entry if it is not already present
in the table. I can get it to work fine if the tbl is local in Access (see
code below) but it does not update the tbl if I move it onto the SQL back end.
What VBA coding differences are there with the SQL route other than setting
key field to INT Indentity, Identity Seed and Identity increment on the SQL
tbl ??

Private Sub Vendor_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
'Prompt user to verify if they wish to add new value
If MsgBox("Vendor " & NewData & " is not in list. Add it ?", vbYesNo) =
vbYes Then

Dim db As Database
Dim rstVendor As Recordset
Dim sqlVendors As String
Set db = CurrentDb()

sqlVendors = "Select * from dbo_Vendors"

Set rstVendor = db.OpenRecordset(sqlVendors, dbOpenDynaset)
rstVendor.AddNew
rstVendor![Vendors] = NewData
rstVendor.Update
Response = acDataErrAdded
MsgBox "The new Vendor has been added to the list"

rstVendor.Close

End If
End Sub

 

[John Spencer]

Try changing this line

Set rstVendor = db.OpenRecordset(sqlVendors, dbOpenDynaset)
to
Set rstVendor = db.OpenRecordset(sqlVendors, dbOpenDynaset, dbSeeChanges)

You didn't say if you got an error message and if you did what was the
error.

Also, it is a good idea to have a TimeStamp field in each table in the SQL
server if you are using Access + ODBC to change (add, modify, delete)
records in that table The upsizer in Access 2003 does this for you (there
is an option to set this to never, always, or automatic?).

 

[dmacpherson via AccessMonster.com]

Try changing this line

Set rstVendor = db.OpenRecordset(sqlVendors, dbOpenDynaset)
to
Set rstVendor = db.OpenRecordset(sqlVendors, dbOpenDynaset, dbSeeChanges)

You didn't say if you got an error message and if you did what was the
error.

Also, it is a good idea to have a TimeStamp field in each table in the SQL
server if you are using Access + ODBC to change (add, modify, delete)
records in that table The upsizer in Access 2003 does this for you (there
is an option to set this to never, always, or automatic?).

The error message is :

Jet D/Base engine cannot find the input table 'dbo_Vendors'. I have checked
the link and it is o.k.

I don't know if this is the correct forum to ask this Q.
Win 2000, Access 2003 SQL Server 2000

[quoted text clipped - 34 lines]

End If
End Sub

 

[John Spencer]

Also, if you are using ODBC to link to the SQL server and DAO library, then
change your declaration to

Dim db As DAO.Database
Dim rstVendor As DAO.Recordset
Dim sqlVendors As String
Set db = CurrentDb()

If you double-click on the linked table - 'dbo_Vendors', does it open and
display data?
If it does, can you manually edit data in the table?
Are you sure about the table name?

Try changing this line

Set rstVendor = db.OpenRecordset(sqlVendors, dbOpenDynaset)
to
Set rstVendor = db.OpenRecordset(sqlVendors, dbOpenDynaset, dbSeeChanges)

You didn't say if you got an error message and if you did what was the
error.

Also, it is a good idea to have a TimeStamp field in each table in the SQL
server if you are using Access + ODBC to change (add, modify, delete)
records in that table The upsizer in Access 2003 does this for you (there
is an option to set this to never, always, or automatic?).

The error message is :

Jet D/Base engine cannot find the input table 'dbo_Vendors'. I have
checked
the link and it is o.k.

I don't know if this is the correct forum to ask this Q.
Win 2000, Access 2003 SQL Server 2000

[quoted text clipped - 34 lines]

End If
End Sub

 

[dmacpherson via AccessMonster.com]

Thank you John ...works fine now..I think it was a whole combination of
things I had not done correctly..

Also, if you are using ODBC to link to the SQL server and DAO library, then
change your declaration to

Dim db As DAO.Database
Dim rstVendor As DAO.Recordset
Dim sqlVendors As String
Set db = CurrentDb()

If you double-click on the linked table - 'dbo_Vendors', does it open and
display data?
If it does, can you manually edit data in the table?
Are you sure about the table name?

Try changing this line
[quoted text clipped - 21 lines]
End If
End Sub