Backend Database

[Wstanley]

I am trying to re-establish a bi-directional link to a backend database. I
have no problems reading from, but I have trouble imprting data into the
tables in the backend database. I developed a system that has security which
I set up using the wizard. I want to distribute the front end as an mde
file. When I first split the database everything worked fine. My problems
all started when I tried to add a table to the backend. I have tried
importing everything into a new clean database and doing the security and the
split all over and it will still not work. I can import the data ino he
tables until I split the database. Can someone please help me. I have been
struggling with this for two weeks. I am using Access 2000. Thank you!

 

[Joan Wild]

I am trying to re-establish a bi-directional link to a backend
database. I have no problems reading from, but I have trouble
imprting data into the tables in the backend database. I developed a
system that has security which I set up using the wizard.

Since you are using 2000, you shouldn't use the wizard (or that alone). It
is flawed and will not secure your database properly. Follow the steps at
http://www.jmwild.com/security97.htm

I want to
distribute the front end as an mde file. When I first split the
database everything worked fine.

If you split after securing it, and using the splitter wizard, then the
backend is not secure at all. You should split it manually.
http://www.jmwild.com/SplitSecure.htm

My problems all started when I
tried to add a table to the backend. I have tried importing
everything into a new clean database and doing the security and the
split all over and it will still not work. I can import the data ino
he tables until I split the database.

Can you be more specific? What doesn't work? Do you get an error message?
What happens?

 

[Wstanley]

Thank you for replying Joan. The specific prblem that I am having is that I
cannot import data into a table after I split the database. Something has
obviously been corrupted because it did work the first time that I split the
database. I tried to be specific and outlined the events that occured and
what I have done to try to resolve the issue. I do not believe that the
error message is relevant, but the message is "numeric field overflow".
There is nothing wrong with the data or the table because I can import it
before I split the databse out and I could do it the first time that I split
the database out. There is no issue with the size of the file or with the
network because it all worked the first time that I split the database. I
have tried to refresh the link to the tables using the menu option. I have
imported everything to a clean databse. I have even reinstalled access.
There must be a way to resolve this issue without having to literally rebuild
the system and programs from scratch. It all started when I tried to add a
table to the backend database. Everything worked fine before that.

 

[Joan Wild]

Thank you for replying Joan. The specific prblem that I am having is
that I cannot import data into a table after I split the database.

What are you importing data from- another database? How are you importing
the data - via a link and append query, via code, just importing a whole
table?

Something has obviously been corrupted because it did work the first
time that I split the database. I tried to be specific and outlined
the events that occured and what I have done to try to resolve the
issue. I do not believe that the error message is relevant, but the
message is "numeric field overflow". There is nothing wrong with the
data or the table because I can import it before I split the databse
out and I could do it the first time that I split the database out.
It
all started when I tried to add a table to the backend database.
Everything worked fine before that.

OK, so you added a table in the backend. Did you then compact the backend
and open the frontend and create a link to this table? Did you then compact
the frontend?

 

[Wstanley]

I am importing data that has been extracted from another system and saved as
a csv file. I am not sure why that matters? As I mentioned, I did this
succesfully before...nothing with the data has changed...in fact the only
change was when I tried to add another table. I am assuming you should be
able to add tables to a backend database and link them to the front end. I
have a macro to handle the import, but that is not the problem because I have
bypassed that and have been trying to import it by using the menu -- File>
Get External Data> Import.

My design consists of importing the data to a temporary table,and performing
some analysis before appending it to the main table. We used the system that
I built daily importing , using all of the forms, code, macros, queries,
etc.. for almost two weeks with no issues. I am not a novice...this really
has me stumped.

I am wondering if I need to insert code to set the databse properties, or
somehow programatically point to the backend etc... Don't people routinely
point to various backend databases and I am certain that others add tables to
backend databases.

I am going to follow your advice on the security...I will revisit that...I
do not think that is causing my problem though.

After your last reply I did compact and repair the back and front end but it
did not resolve the issue. I wonder if I need to try to do this from another
PC. I have reinstalled ACCESS 200 twice, but maybe there is something
somewhere on my pc that is causing this problem.

I am open to suggestions and will try whatever you or anyone may suggest.

 

[Joan Wild]

I am importing data that has been extracted from another system and
saved as a csv file. I am not sure why that matters?

and also

I do not believe that the error message is relevant, but the
message is "numeric field overflow".

You need to remember that we cannot see your database, nor see everything
that you see or have done.

Numeric field overflow usually indicates that you are attempting to add data
to a field that isn't large enough. On your first import, Access could have
chosen an Integer by default for some field. Your second or subsequent
import may have data that is larger than this, causing the error. Could you
check the field sizes in the table you've created?

I don't believe that it is related to your database being split.

As I
mentioned, I did this succesfully before...nothing with the data has
changed...in fact the only change was when I tried to add another
table.

Again check the properties of this table.

My design consists of importing the data to a temporary table,and
performing some analysis before appending it to the main table.

Check the properties of this table as well to make sure things match wrt
field sizes.

I am going to follow your advice on the security...I will revisit
that...I do not think that is causing my problem though.

Nor do I.

After your last reply I did compact and repair the back and front end
but it did not resolve the issue.

Also refresh the links in the frontend.

 

[Wstanley]

Thank you Joan for all of you help. I resolved my issue by importing the
data into the front end and then appending it to the table in the back end.
We are testing it and everything appears to be working fine. I did use your
guide on setting the security manually in Access 2000 and it worked great!

I also read the discussion thread you had with another person some time back
regarding disabling the by pass key at start up. It seems she was never able
to get it to work and neither have I. I followed the discussion and tried
everything you instructed her to try. I use the code that Michael Kaplan
wrote, checked to ensure that reference to Microsoft DAO 3.6 Objecft Library
was checked, and tried running the function before and after I created the
mde and could never disable the shift key bypass at start up.

I noticed that there are many items that are unchecked in the Tools,
References box. Is it possible that I need to check off another reference?
There are only about 6 items checked off in the available references.

Thanks

 

[Joan Wild]

I also read the discussion thread you had with another person some
time back regarding disabling the by pass key at start up. It seems
she was never able to get it to work and neither have I. I followed
the discussion and tried everything you instructed her to try. I use
the code that Michael Kaplan wrote, checked to ensure that reference
to Microsoft DAO 3.6 Objecft Library was checked, and tried running
the function before and after I created the mde and could never
disable the shift key bypass at start up.

I assume you are referring to the code at
http://www.mvps.org/access/general/gen0040.htm

I have never had a problem using this code. You realize that you just run
it once, and it takes effect the next time you open the database?

Is it possible that you have code on startup that displays the database
window (like the switchboard manager creates)?

I noticed that there are many items that are unchecked in the Tools,
References box. Is it possible that I need to check off another
reference? There are only about 6 items checked off in the available
references.

Well I generally only have 3 or 4, so that's not likely the problem.