Rx_
Nothing In Moderation
- Local time
- Today, 02:37
- Joined
- Oct 22, 2009
- Messages
- 2,803
Question:
dbs.Execute strSQL, dbFailOnError Or dbSeeChanges
Set rs2Add = dbs.OpenRecordset("Select * from dbo_Invoices", dbOpenDynaset, dbAppendOnly Or dbSeeChanges)
In the code maintenance, there was open dbsnapshot, dbAppendOnly, and other choices.
I added the OR dbSeeChanges to each of them
I don't see any downside, the tables moved to SQL Server work now.
Does anyone know of any downside that I should consider?
FYI for those moving Access Tables to SQL Server
error 2544 "You entered an expression that has an invalid reference to the property Form/Report." (This isn't well documented as a reason)
Some code handed down to me. The Form with several subforms wouldn't opent due to error 2544.
Opened each of the sub-forms one at a time and they appeared to work fin. Try to open them from the parent form and this error pops up.
The error was on a form open event where it clears out a table that drives a subform. Each of the three tables for each of three subforms had been prototyped using local MS Access tables. That copy of the project worked fine. The three tables had been migrated to MSSQLServer. The copy with the linked tables didn't work.
Solution:
One of the SQL Server Tables had the identity set correctly for the autocounter. But, the person forgot to set that field to the Primary Key.
The Linked Table looked OK in every way, except the linked table couldn't add a new row.
The Main Form open was attempting to refresh the sub form - where the sub form Linked Table was a read-only table since the Primary Key was missing.
Other Linked Table Message
Once the Primary Key was set on the SQL Server side, and the link table was refreshed, the error went away. But, a second error appears.
error #3622 You must use the dbSeeChange Option with Open Recordset when accessing a SQL Server table that has an IDENTITY column.
I bring this up as a reminder to those who prototype in Access tables then move the tables to SQL Server later.
dbs.Execute strSQL, dbFailOnError Or dbSeeChanges
Set rs2Add = dbs.OpenRecordset("Select * from dbo_Invoices", dbOpenDynaset, dbAppendOnly Or dbSeeChanges)
In the code maintenance, there was open dbsnapshot, dbAppendOnly, and other choices.
I added the OR dbSeeChanges to each of them
I don't see any downside, the tables moved to SQL Server work now.
Does anyone know of any downside that I should consider?
FYI for those moving Access Tables to SQL Server
error 2544 "You entered an expression that has an invalid reference to the property Form/Report." (This isn't well documented as a reason)
Some code handed down to me. The Form with several subforms wouldn't opent due to error 2544.
Opened each of the sub-forms one at a time and they appeared to work fin. Try to open them from the parent form and this error pops up.
The error was on a form open event where it clears out a table that drives a subform. Each of the three tables for each of three subforms had been prototyped using local MS Access tables. That copy of the project worked fine. The three tables had been migrated to MSSQLServer. The copy with the linked tables didn't work.
Solution:
One of the SQL Server Tables had the identity set correctly for the autocounter. But, the person forgot to set that field to the Primary Key.
The Linked Table looked OK in every way, except the linked table couldn't add a new row.
The Main Form open was attempting to refresh the sub form - where the sub form Linked Table was a read-only table since the Primary Key was missing.
Other Linked Table Message
Once the Primary Key was set on the SQL Server side, and the link table was refreshed, the error went away. But, a second error appears.
error #3622 You must use the dbSeeChange Option with Open Recordset when accessing a SQL Server table that has an IDENTITY column.
I bring this up as a reminder to those who prototype in Access tables then move the tables to SQL Server later.
