Trouble adding a new record to a split database

[Guest]

I can't add any new records using the front end of my Access 2003 database.
I have split it into front and back ends, the back end is in a different
folder and the tables seem to be correctly linked. In the front end forms,
the new record buttons are greyed out and don't do anything. An "add new
record" control button on the forms returns the error message, "You can't go
to the specified record."

If I open the back-end tables I can add new records, but it would be much
easier to use the forms in the front end. It is a fairly complicated
database with multiple linked tables and several forms.

I can see all of the existing records through the front-end forms, I just
can't add new ones.

I have checked the properties on the form selector window and they are all
set to allow new records, edits, deletions, etc. I've run out of things to
look at.

Any suggestions?

 

[John W. Vinson]

On Tue, 19 Jun 2007 10:09:38 -0700, K Roeder <K

I can't add any new records using the front end of my Access 2003 database.
I have split it into front and back ends, the back end is in a different
folder and the tables seem to be correctly linked. In the front end forms,
the new record buttons are greyed out and don't do anything. An "add new
record" control button on the forms returns the error message, "You can't go
to the specified record."

Sounds like something wrong with the button. Are the builtin navigation
buttons turned on? If so do they work? If not, try turning them on and see if
the new-record button works; and/or post the "Add New Record" button code.

Does Compacting the database help? How about decompiling?

If I open the back-end tables I can add new records, but it would be much
easier to use the forms in the front end. It is a fairly complicated
database with multiple linked tables and several forms.

Can you add data to the linked Tables, or do you have to actually open the
backend database?

One possible difference: in a local table you can use a "table type recordset"
in VBA (dbOpenTable). For a linked table you must use a Dynaset
(dbOpenDynaset) instead. Not sure if your forms use such code as opposed to
being simple bound forms but it is one possible cause of this problem.

John W. Vinson [MVP]

 

[Guest]

Thanks for the suggestions.
Most of what I've learned about Access came from a "For Dummies" book, so
some of your suggestions are over my head but I have a couple of things to
try now. More comments below...

On Tue, 19 Jun 2007 10:09:38 -0700, K Roeder <K


Sounds like something wrong with the button. Are the builtin navigation
buttons turned on? If so do they work? If not, try turning them on and see if
the new-record button works; and/or post the "Add New Record" button code.

I don't think it's a problem with the button. The other add record buttons
built in to the standard toolbars don't work either.

Does Compacting the database help? How about decompiling?

Compacting didn't help, that was one of the first things I tried. I'm not
sure what you mean by decompiling, (I 'm not good with computer lingo.)

Can you add data to the linked Tables, or do you have to actually open the
backend database?

I need to open the back end database to add new records. I have the same
problem with all of the linked tables that I was having with the forms. It
seems to be a universal problem in the front end of the database--I can't add
new records anywhere.

One possible difference: in a local table you can use a "table type recordset"
in VBA (dbOpenTable). For a linked table you must use a Dynaset
(dbOpenDynaset) instead. Not sure if your forms use such code as opposed to
being simple bound forms but it is one possible cause of this problem.

This sounds like a great suggestion. I'm a newbie with VBA code so I'm a
little reluctant to spend too much time on it unless it's fairly
straightforward and I have a reasonably good chance of success. I might be
able to find someone here to help me with it. Thanks

John W. Vinson [MVP]

K Roeder

 

[John W. Vinson]

I need to open the back end database to add new records. I have the same
problem with all of the linked tables that I was having with the forms. It
seems to be a universal problem in the front end of the database--I can't add
new records anywhere.

Then there's something REALLY wrong. Either the linking is not set up
correctly, or you have a security issue. You need to have the Windows security
set so that the user of the frontend has *full* read, write, create and delete
privileges on the folder containing the backend (so Access can create and
delete the .ldb locking file). Is that the case? If you open the frontend, and
look at the Tables tab in the database window, do you see the blue-arrow icon
by each table indicating that it is linked?

John W. Vinson [MVP]

 

[Guest]

Fixed it.
It was a linked table problem. My server administrators moved my files over
to a new drive to free up space and the new front-end copy was still linked
to the old back-end. The old files were still there but they changed the
whole drive to read-only causing my inability to add a new record. It looked
ok, but I was really seeing the old version instead of the active back-end
that had been coppied over to the new drive.

Looks like I can take that coppy of "VBA For Dummies" back to the library.

Thanks for pointing me in the right direction.
-K