HOW TOP COPY TABLE FROM UNPROTECTED DATABASE INTO PROTECTED DATABASE?

[pelagic]

Hi there
I have 2 Access 2000 databases DB1.mdb and DB2.mdb
DB1.mdb is unprotected, but DB2.mdb is protected with password. The password is "mypassword".

In DB1.mdb there is a table named table1 and in
DB2.mdb there is a table named table2.

I would like to copy all data from table1 in DB1.mdb into
table2 in DB2.mdb

Is there a way to do this?
I have tried many different ways but it doesnot work.

This is what I have in VB:
Dim strPathtoDB1 As String 'path to database
Dim strPathtoDB2 As String
Dim cn1 As ADODB.Connection
Dim cn2 As ADODB.Connection
Set cn1 = New Connection
Set cn2 = New Connection

strPathtoDB1 = App.Path & "\DB1.mdb" 'unprotected database
cn1.CursorLocation = adUseClient
cn1.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPathtoDB1

strPathtoDB2 = App.Path & "\DB2.mdb" 'protected database
cn2.CursorLocation = adUseClient
cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoDB2 & _
";Jet OLEDBatabase Password=mypassword"

cn1.Execute "INSERT INTO table2 IN DB2.mdb SELECT * FROM table1"

Thanks in advance,
pel

 

[willir]

Pel

Do you have to use code? If this is a one shot deal, have you considered importing the tables using the menu option "File" -> "Get External Data" -> Import

If this is an on-going excercise, then using code is the proper way to go about the task.

 

[pelagic]

Hi there,
Can you import a table from unprotected Access 2000 database into Access 2000 protected database?

For example:
in DB1.mdb there is a table table1. I would like to
import this table into DB2.mdb using ADO.
DB2.mdb is a protected database with password.
The password is "mypassword".

Is there a way to do it?

Thanks

 

[willir]

Pel
I am assuming you are in the Access 2000 protected database. It should work with one exception. If the "protection" includes converting the database to an MDE statnd alone application, then you can not. You have to work with the normal MDB database object (which can have still have security).

by "protected", do you mean a database with an suffix MDE format?

 

[pelagic]

Hi Willir,
by "protected" means the database has password protected like set database password. In Access database:
1. From the Tools menu
2. Security
3. Set database password.

In this case DB2.mdb has password protected
DB1.mdb does not has password protected.
Now can I still import table/tables from DB1.mdb into
DB2.mdb using ADO. I have tried using ADO code to copy but I have an error message saying that "not valid password" even though I have included the password into the code corrected. Please look above example.

Thank you for your time.

 

[willir]

Pel

Good, you are not working with an MDE file. Assuming you have admin access, yes you should be able to import the data.

Again, can you use the import menu item instead of coding -- a heck of a lot easier. If you need to use code, I do not see the User ID component. (I am not strong on ADO - I presonally prefer DAO)

I hope this is not a school assignment...

 

[pelagic]

Hi Willir,
Even with DAO code do you have any code examples or any links that can point me to?
I have to use code to do the import.
Import from the file menu is not allowed.

thanks
Pel

 

[HowAboutThis]

Here is the DAO snippet you are looking for.

MySql = "Select * INTO [;database=C:\SOURCE\DB2.mdb;pwd=mypassword].[Table2] From [Table1];"

'MyDB1 is an open database connection to DB1
MyDB1.execute mysql, dbfailonerror

You can also use this same technique to join tables across different databases.