Unable to add records to SQL Server BE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Front End MDB that runs agains a Back End MDB. I am converting the
back end to SQL Server. The front end is too large to rewrite.

I am having relative success, having converted all my OpenRecordsets to use
"dbOpenDynaset, dbSeeChanges" and my "Docmd RunSQL" commands are now
"DB.Execute StrSQL, dbSeeChanges". I can move through my forms looking at
records and can change values in any record.

BUT, I cannot add records. Even though my form properties say I can edit
and Add and Delete records, the New Record button in the Record Navigators is
always grayed out. What am I missing?
 
Do you have a primary key created in the SQL Server table? If so, does
Access recognize it?
 
Interestingly, I had a primary key in the MDB Back End, but when I used the
upsizing wizard, the primary key never made it into the SQL Server Back end!

Specifying a Primary Key in the tables in the back end did the trick. Thanks!
 
When you export tables from Access to SQL server, the keys wont transfer with
the table.
The only things that will be transfered are the fields and the data.
Just a note incase you plan to transfer tables from Access to SQL
 
Well, I assigned a primary key in the SQL Server table and I can add records.
Now, I have a sub form on the original form, and even though there is a
primary key in the table that feeds the sub form, I cannot add records to
that table from the sub form.
 
Bill Sturdevant said:
I have a Front End MDB that runs agains a Back End MDB. I am converting the
back end to SQL Server. The front end is too large to rewrite.

I am having relative success, having converted all my OpenRecordsets to use
"dbOpenDynaset, dbSeeChanges" and my "Docmd RunSQL" commands are now
"DB.Execute StrSQL, dbSeeChanges". I can move through my forms looking at
records and can change values in any record.

BUT, I cannot add records. Even though my form properties say I can edit
and Add and Delete records, the New Record button in the Record Navigators is
always grayed out. What am I missing?
Click to expand...
 
I also do not have time to re-write the Access front end. Is this all you
have to do after running the upsizing wizard to create a backend sql?
 
I wish.

The extra work all depends on how the original is designed and how many
queries and how complex they are. Among other things.

(For example: Almost all queries with critia coming from a form will
need to be re-written.)

make a backup, and try the conversion. You will find out fairly quickly
if it will work and you still have your backup.
 
Bummer. I have many complex queries. When you say re-written, do you mean
using Sql server views? Does all Access code need to be updated to use ADODB
data access libraries?
 
Conditional sum queries do not convert.
Most queries that point back to forms as criteria will not convert
certain types of append types of queries will not convert
Any module functions that read and/or append and/or update tables will
need to be rewritten. These atleast are still there, but just don't
work. The queries disappear.

Basically, 95% of the tables convert properly,
All the forms convert properly.
For my conversion, only about 50% of the queries converted.

Those were my results, but your's may be better. Conversion does not
distroy anything (if you convert a copy then guarenteed nothing is
affected). Try it, you may have better results.
 
Back
Top