Cannot make a table to a password protected database.

Question

In Access 97 and Access 2002, I regularly use the Make Table query, and direct the output to a back end database.

For some reason, I am getting error messages if, and only if, the back end database has a password.

I've tried this in Access 97 and Access 2002. In Access 97, I use the Make Table query and specify the back end database. It then prompts me for the password and it accepts it. When I run the query, it says, "No database specified in connection string or IN caluse." In Access 2002, it accepts my password at the prompt, but says, "Not a valid password" when I exexcute the query. My password is only 8 digits long. Has anyone else received this error? Is it associated with a Windows update? It seems like my collegues are also getting this error. I am just using the Query>Make Table option, and not using SQL. Thank you!!

Answer 1

Sure, I can give you more information.

I have a front end database with mainly forms, queries and reports that about 50 users use. I have been using this database for managing kidney patients here (an HMO) for about 12 years. I have linked tables to about 5 other Access databases. It is necessary for me to keep the tables in a variety of databases for a number of reasons. One is, the size of the tables would make my front end database too big. Secondly, due to the number of users, I do not want to have to compact/repair more than once a month. Lastly, I am updating the database with clinical information that I am receiving from other systems, and are updated once a month, so the back end tables are changing monthly.

So I have about 25 main tables that are linked to my database, and since I am working with confidential information, all of the back end databases need to be password protected. Every month I run a macro, which takes about a day to run, and has many queries. Some of these queries join my tables and need to create new tables to be stored in one of my password protected databases. I just got a new computer, so it is possible some kind of Windows update is causing the error.

Does this work on your computer? It took me about 2 minutes to test it on my desktop, I just created one password protected database, and another database, and tried to run a make table query that sent the table to the password protected database. I get the error every time, regardless of the name of the database, location of the database, or version of Access I use. I am using all the built in features of MS Access, no custom SQL or anything like that. The only thing I entered were the table name, location and name of database, and the password when prompted. Thank you.

Answer 2

In Access 97 and Access 2002, I regularly use the Make Table query, and direct the output to a back end database.

For some reason, I am getting error messages if, and only if, the back end database has a password.

I've tried this in Access 97 and Access 2002. In Access 97, I use the Make Table query and specify the back end database. It then prompts me for the password and it accepts it. When I run the query, it says, "No database specified in connection string or IN caluse." In Access 2002, it accepts my password at the prompt, but says, "Not a valid password" when I exexcute the query. My password is only 8 digits long. Has anyone else received this error? Is it associated with a Windows update? It seems like my collegues are also getting this error. I am just using the Query>Make Table option, and not using SQL. Thank you!!

 Make tables should really only be happening inside the FRONTEND database.  Why do you regularly use make tables to create a table in the backend?  I really don't understand what your set up is like and why you would be doing something like that.  It is RARE that you should be needing to add tables to a backend database as a properly designed database doesn't normally have tables created except on rare occasion.

So, what is it you are doing and why?  We might be able to steer you to a good path if we know.