July 8, 2005 at 9:29 am
I have an Access 2000 database that I upsized to SQL. I also kept a copy of the original database. The tables now show as linked SQL tables. I have a form that is used to imput weekly billing data and it is based on a query. In the Access 2000 version of the database users can add new records via the form with no problem. In the upsized database users are unable to add new records. It does not produce an error, just if you click the new record button the form is blank, no fields or anything. I've checked the form and query properties and there are no locks on either.
Any ideas on what could be causing this problem or what I could look at?
July 8, 2005 at 9:34 am
is there any vb code behind the form to generate the recordset?
or is it just a collection of fields that are data bound to the query.
if you upsize access to SQL 2000 and you use ado.recordsets in your code then you need to add a few parameters to acount for the different locking mechanisms.
as i remember rightly one of these is dbDynasetSeeChanges
MVDBA
July 8, 2005 at 9:41 am
the other ones i forgot to mention that just came back are...
if you have BIT/Boolean fields in SQL server and they are linked to Access then you must add a timestamp type column to the SQL server table and re-link the tables (something to do with tri-state boolean compatability)
did you choose the correct primary key when you linked your tables in access to the SQL server - Access 2000 does not identify the Primary keys of a table when you link and you must correctly choose the PK
if you don't choose a pk then you can't add a record.
MVDBA
July 8, 2005 at 9:42 am
Mike,
No VB code, just fields bound to a query. And no I did not create this. This was left to me by a former IT worker in the company. When I inherited the database it was in Access 2.0. I've upgraded it to Access 2003, but now my boss wants all databases to use SQL.
July 8, 2005 at 9:47 am
The upsize wizard did not give any option to choose the primary key. The SQL tables (2) are using the same primary keys as the Access tables.
July 8, 2005 at 9:54 am
here's my advice.
go through enterprise manager and ensure all your tables have primary keys
drop all of your linked tables in access
re-link all of your tables in access - it may then prompt you for the primary key of each table. you will then need to specify the key as it is displayed in Enterprise manager
at the same time check any bit fields (yes/no) in access and ensure that if there is a bot column in SQL that there is also a timestamp column (one per table)
fingers crossed that should do it.
MVDBA
July 9, 2005 at 8:51 am
I didn't know about the timestamp thing. I'm pretty sure I've upsized boolean fields without adding at timestamp field with no difficulties, but I may be mistaken. Does anyone else have experience with this?
Aaron
July 11, 2005 at 2:49 am
I think the key thing here is that the Upsize is USELESS
I make a point of always re-linking my tables.
it's the same with views - create a view that is just a select * from mytable (or whatever)
if you link this in as a table it will ask for the key. If yoou don't specify the key - no updates, no deletes, no inserts
MVDBA
July 13, 2005 at 8:39 am
You need to define primary keys (unique indexes) for all related tables in SQL server. I had the same problem.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply