 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Security / April 2008Append Query
I am unable to run an append query that copies records to a destination database containing a password. I'm using Access 2007 and both the source and destination databases are saved in Access 2003. The append query copies all records from table A in the source database to table A in the destination database. The fields in the two tables are identical. If the destination database has no password, the append query runs correctly. I add a password to the destination database, then in the source database I point the append query to the destination database and I add the password as required by a dialog box. But when I run the query, I get a message box saying "Not a valid password". The error is 3031. How can I transfer records to an existing table in a destination database that is protected by a password? Thanks. Jim The password has to be saved in the query, not typed in later when the query runs. Open the query in design view and add the path and password to the Dest Connect Str property using this syntax: ;DATABASE=C:\MyPath\Mydb.mdb;PWD=Mypwd Chris Microsoft MVP >I add a password to the destination database, then in the source database I >point the append query to the destination database and I add the password as [quoted text clipped - 3 lines] >How can I transfer records to an existing table in a destination database >that is protected by a password? I am unsure how to set this property other than the actions I have taken. I open the database, open the query in design view, click Design in the menu and note that Append is selected under Query Type. I click Append and the Append dialog box opens. It already shows the table name, the radio button "Another Database" is selected and File Name displays: C:\MyPath\Mydb.mdb;"PWD=Mypwd" If I open the Properties Sheet, no data displays. I'm unsure what to try next. Jim > The password has to be saved in the query, not typed in later when the query > runs. Open the query in design view and add the path and password to the [quoted text clipped - 12 lines] > >How can I transfer records to an existing table in a destination database > >that is protected by a password? When the query is in design view and you see the query grid, right-click in the upper panel and select properties. The Dest Connect Str property is empty. You must type the connection string in that property using the following syntax: ;DATABASE=C:\MyPath\Mydb.mdb;PWD=Mypassword The Destination Table property may be empty, but it must have the name of the table in the destination database: TableA Save it and close the properties sheet, then run the query. Chris Microsoft MVP >I am unsure how to set this property other than the actions I have taken. > [quoted text clipped - 15 lines] >> >How can I transfer records to an existing table in a destination database >> >that is protected by a password? With the append query in design view, I right-click on the upper panel and select Properties. That displays a property sheet that shows Selection Type: Field List Properties. It's empty. If I click again on the upper pane, the property sheet displays query properties. Destination Table contains the correct table name. Destination DB contains the path to the db. I enter the string described below for Dest Connect Str. I run the query and again get the message "Not a valid password". I then remove the password from the destination db and delete the password reference at the end of the string for Dest Connect Str. Now, the query appends records correctly. Jim > When the query is in design view and you see the query grid, right-click in > the upper panel and select properties. The Dest Connect Str property is [quoted text clipped - 32 lines] > >> >How can I transfer records to an existing table in a destination database > >> >that is protected by a password? Yes, you need to remove the password to access the data. After you have finished with the spreadsheet, remember to set the password again. I suppose this means that the password does work to protect the data in the excel sheet - otherwise there would not be much advantage to setting a password in the first place. Jeanette Cunningham > With the append query in design view, I right-click on the upper panel and > select Properties. That displays a property sheet that shows Selection [quoted text clipped - 59 lines] >> >> >database >> >> >that is protected by a password? JBA is using Access 2007 and 2003 databases, not spreadsheets. If you need to remove the password in order to work with the data, you're doing something wrong. If you remove the password while you're working with the data, then anyone else with rights to that file can access that data. That's like leaving the padlock unlocked. Chris Microsoft MVP >Yes, >you need to remove the password to access the data. After you have finished [quoted text clipped - 10 lines] >>> >> >database >>> >> >that is protected by a password? Remove the path and file name in the Destination DB property. It should be empty. In the Dest Connect Str property, change the path, file name, and password to match your own destination database. The ones I suggested earlier were only for an example. Chris Microsoft MVP >With the append query in design view, I right-click on the upper panel and >select Properties. That displays a property sheet that shows Selection Type: [quoted text clipped - 9 lines] >reference at the end of the string for Dest Connect Str. Now, the query >appends records correctly. I removed everything from the Destination DB property. The Dest Connect Str already has my info. I still get the "Not a valid password" message. To try to get something to run, I set up this test: I use Access 2007 only, the source db is D1, the destination db is D2, both databases have the same table called Table1 and a single field called Field1. D2 is pw protected with pw=me. D1 is not pw protected. I set up the append query in D1 with Destination Table=Table1, Destination DB is empty and Dest Connect String=;DATABASE=C:\Documents and Settings\James\My Documents\D2.accdb;PWD=me. I run the query and get the "Not a valid password" message. The SQL is: INSERT INTO (;DATABASE=C:\Documents and Settings\James\My Documents\D2.accdb;PWD=me ) Table1 ( Field1 ) SELECT Table1.Field1 FROM Table1; Then, I remove the pw from D2, and modify the append query so it's properties are Destination Table=Table1, Destination DB=C:\Documents and Settings\James\My Documents\D2.accdb and Dest Connect String is empty. The query runs correctly. I'm unsure what to try next. Thanks. Jim > Remove the path and file name in the Destination DB property. It should be > empty. [quoted text clipped - 19 lines] > >reference at the end of the string for Dest Connect Str. Now, the query > >appends records correctly. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.