INSERT INTO excel interface with two password protected databases

J

jrdnoland

Registered User.
Local time
Today, 09:53
Joined
Dec 13, 2009
Messages
37
I'm using an excel interface, ado, and sql.

I have two access 07 databases that I'm trying to append a record from one into the other.

The receiving database runs the generated sql with this:

Code: 
Public Function Run_AppendQuery(SQLSTRING As String)
Dim cnt As ADODB.Connection
Dim stCon As String
Dim StrDB As String
On Error GoTo ErrHandle:
StrDB = c_Drive & c_MainFolder & c_SubFolder_RMS & c_SubFolder_RMS_PrimDB & c_DBName_RMS_PrimDB  'Primary
stCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & StrDB & ";Persist Security Info=False;Jet OLEDB:Database Password=roscoe;"

Set cnt = New ADODB.Connection
cnt.Open stCon
cnt.Execute SQLSTRING
cnt.Close
Set cnt = Nothing

ExitHere:
Exit Function

ErrHandle:
    Dim cnErrors As ADODB.Errors
    Dim ErrorItem As ADODB.Error
    Dim stError As String
    Set cnErrors = cnt.Errors
   With Err
        stError = stError & vbCrLf & "VBA Error # : " & CStr(.Number)
        stError = stError & vbCrLf & "Generated by : " & .Source
        stError = stError & vbCrLf & "Description : " & .Description
        MsgBox Err.Description
   End With
    For Each ErrorItem In cnErrors
        With ErrorItem
            stError = stError & vbCrLf & "ADO error # : " & CStr(.Number)
            stError = stError & vbCrLf & "Description : " & .Description
            stError = stError & vbCrLf & "Source : " & .Source
         stError = stError & vbCrLf & "SQL State : " & .SqlState
        End With
    Next ErrorItem
    If InStr(stError, "3021") > 0 Or InStr(stError, "13") > 0 Or InStr(stError, "2147") > 0 Then
        Resume Next
    Else
        MsgBox stError, vbCritical, "SystemError"
        Resume ExitHere
    End If
i_Err:
    MsgBox "There are no Specifications for this Query.", vbInformation, "Query Info"
    GoTo ExitHere
End Function
The insert into is taking a record from the second database and trying to append it to the first (the one above). The record from the second db then gets deleted (that works).

This is a generated sql:

Code: 
 INSERT INTO   tblManufacturersSecondary(P_Man_Numb_Revisions,P_Man_Stock_Code,P_Man_Iteration,P_Man_Name,
   P_Man_Trade_Name,P_Man_Address,P_Man_City,P_Man_State,P_Man_Zip,P_Man_Phone,P_Man_Fax,P_Man_Website,
   P_Man_Comments,P_Man_Expiry) 

   IN 'D:\RMS Database System\Raw Material Specification\Primary RMS Database\
   Raw Material Specification Primary Database.accdb'

  SELECT tblManufacturers.Man_Numb_Revisions,tblManufacturers.Man_Stock_Code,tblManufacturers.Man_Iteration,
   tblManufacturers.Man_Name,tblManufacturers.Man_Trade_Name,tblManufacturers.Man_Address,
   tblManufacturers.Man_City,tblManufacturers.Man_State,tblManufacturers.Man_Zip,tblManufacturers.Man_Phone,
   tblManufacturers.Man_Fax,tblManufacturers.Man_Website,tblManufacturers.Man_Comments,
   tblManufacturers.Man_Expiry 

   FROM tblManufacturers 

   IN 'D:\RMS Database System\Raw Material   Specification\Secondary RMS Database\
   Raw Material Specification Secondary   Database.accdb;PWD=roscoe;' 

   WHERE (((tblManufacturers.Man_Stock_Code)='0804-440' AND   (tblManufacturers.Man_Numb_Revisions)=5 
   AND (tblManufacturers.Man_Iteration)=1));
I can't seem to get the second database to open up with the supplied password.

I get an error message stating that the password in wrong or that it cannot find the file.

What is the syntax to run a sql that will append a record from one access 07 database to another.

I've even tried to let access generate the sql but it still doesn't work. Note the spacing is wrong in the second code block; it's correct in my application, I tried to make it easier to see.
 
IN 'D:\RMS Database System\Raw Material Specification\Secondary RMS Database\ Raw Material Specification Secondary Database.accdb;PWD=roscoe;'
Click to expand...

Your ending the quotation mark at the wrong place. It should be;

IN 'D:\RMS Database System\Raw Material Specification\Secondary RMS Database\ aw Material Specification Secondary Database.accdb';PWD=roscoe;
 
jal said:
Your ending the quotation mark at the wrong place. It should be;

IN 'D:\RMS Database System\Raw Material Specification\Secondary RMS Database\Raw Material Specification Secondary Database.accdb';PWD=roscoe;
Click to expand...


jal - thank you for taking the time to look at this.

When I tried your suggestion it still doesn't work and the error description generated was:

Characters found after end of the SQL Statement

Any other thoughts?
 
Oh, sorry, I just remembered that passwords are usually specified in the connection string, not in the SQL. I'm an idiot. And I have no experience with passwords. I'm guessing, if the two databases have the same password, the initial connection string should suffice (i.e. there should be no need to try using the SQL to specify the password). If they have different passwords, then I'm at a loss as to what to do. Sorry, here my lack of experience is showing.
 
Re-reading your code, you seem to be sharing the same password accross the two databases. Doesn't the initial connection string suffice, then?
 
jal said:
Re-reading your code, you seem to be sharing the same password accross the two databases. Doesn't the initial connection string suffice, then?
Click to expand...


thanks jal; no the initial connection string won't cover both of the databases. The password is supplied in the initial connection string that is the receiving database but the supplying database is also password protected.

I think I tried that approach and it didn't work, but I'll look at it again.


Below is what i can find from microsoft, but it's still not clear about supplying the passwords.


Multiple-record append query:
INSERT INTO target [(field1[, field2[, …]])] [IN externaldatabase] SELECT [source.]field1[, field2[, …] FROM tableexpression
Single-record append query:
INSERT INTO target [(field1[, field2[, …]])] VALUES (value1[, value2[, …])
The INSERT INTO statement has these parts:
PartDescriptiontargetThe name of the table or query to append records to.field1, field2Names of the fields to append data to, if following a target argument, or the names of fields to obtain data from, if following a source argument.externaldatabaseThe path to an external database. For a description of the path, see the IN clause.sourceThe name of the table or query to copy records from.tableexpressionThe name of the table or tables from which records are inserted. This argument can be a single table name or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN operation or a saved query.value1, value2The values to insert into the specific fields of the new record. Each value is inserted into the field that corresponds to the value's position in the list: value1 is inserted into field1 of the new record, value2 into field2, and so on. You must separate values with a comma, and enclose text fields in quotation marks (' ').

To identify a destination table:
[SELECT | INSERT] INTO destination IN {path | ["path" "type"] | ["" [type; DATABASE = path]]}
To identify a source table:
FROM tableexpression IN {path | ["path" "type"] | ["" [type; DATABASE = path]]}
A SELECT statement containing an IN clause has these parts:
PartDescriptiondestinationThe name of the external table into which data is inserted.tableexpressionThe name of the table or tables from which data is retrieved. This argument can be a single table name, a saved query, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.pathThe full path for the directory or file containing table.typeThe name of the database type used to create table if a database is not a Microsoft Access database engine database (for example, dBASE III, dBASE IV, Paradox 3.x, or Paradox 4.x).
 
Since the password is supplied in the connection string, could I have two connection strings?

How would that work?

Would I open the second database and then run the SQL through the primary connection?

Not sure, but it's worth looking into.
 
Certainly I've tried this without passwords, and the initial connection string sufficed for all the (.MDB) databases. About that I have no doubt, as I've got a couple of programs using that method right now (written in C#.Net).
 
I tried using two connections. I can see both of them open. I still get the error; invalid password.

I know the passwords, I even tried several different passwords.

I'm stumped, can't find much on line to help either.
 
Try again to let the one connection string suffice for the two databases. But you first need to fix something in your code. You specified two paths. But you should only need to specify one path.

Code: 
INSERT INTO   tblManufacturersSecondary(P_Man_Numb_Revisions,P_Man_Stock_Code,P_Man_Iteration,P_Man_Name,    P_Man_Trade_Name,P_Man_Address,P_Man_City,P_Man_State,P_Man_Zip,P_Man_Phone,P_Man_Fax,P_Man_Website,    P_Man_Comments,P_Man_Expiry)    [COLOR=Red]  IN 'D:\RMS Database System\Raw Material Specification\Primary RMS Database\    Raw Material Specification Primary Database.accdb[/COLOR]'    SELECT tblManufacturers.Man_Numb_Revisions,tblManufacturers.Man_Stock_Code,tblManufacturers.Man_Iteration,    tblManufacturers.Man_Name,tblManufacturers.Man_Trade_Name,tblManufacturers.Man_Address,    tblManufacturers.Man_City,tblManufacturers.Man_State,tblManufacturers.Man_Zip,tblManufacturers.Man_Phone,    tblManufacturers.Man_Fax,tblManufacturers.Man_Website,tblManufacturers.Man_Comments,    tblManufacturers.Man_Expiry      FROM tblManufacturers      [COLOR=Red]IN 'D:\RMS Database System\Raw Material   Specification\Secondary RMS Database\    Raw Material Specification Secondary   Database.accdb;PWD=roscoe;'   [/COLOR]   WHERE (((tblManufacturers.Man_Stock_Code)='0804-440' AND   (tblManufacturers.Man_Numb_Revisions)=5     AND (tblManufacturers.Man_Iteration)=1));


The engine already "knows" the path to the DB specified in the connection string so you only have to specify one path - the path to the DB that is NOT named in the connection string. Example:

INSERT INTO NPIs
SELECT NPI FROM tblNPIs IN 'C:\Denials.mdb'

The database "Denials.MDB" was NOT named in the connection string. The above is real code that I actually used in a program - it DOES work (although I didn't have passwords).
 
I tried it with .Mdb files and got a similar error. This sort of thing seems to run into a problem when passwords are involved. This thread proposes a solution but it doesn't seem to work for me.

INSERT INTO [;DATABASE=d:\Apppath\local.mdb;PWD=tej].InvoiceItems
SELECT InvoiceItems.*
FROM InvoiceItems
WHERE (((InvoiceItems.ii_InvoiceNo)='B416'));

http://forums.devx.com/showthread.php?t=51334
 
I got it working on my machine. Here's the code:

Code: 
 Dim cn As New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;  Jet OLEDB:Database Password=access;Data Source=C:\Names1.mdb"
    cn.Open
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
   cmd.CommandText = "INSERT INTO  [;Database=C:\Names2.mdb;  pwd =access].[Names]  SELECT * FROM  [Names] "
    cmd.Execute
    cn.Close
    MsgBox "Sucess"
 
This also worked (this time I insert into the first db):

Code: 
 Dim cn As New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;  Jet OLEDB:Database Password=access;Data Source=C:\Names1.mdb"
    cn.Open
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
   cmd.CommandText = "INSERT INTO  [Names] SELECT * FROM  [;Database=C:\Names2.mdb;  pwd =access].[Names]  "
    cmd.Execute
    cn.Close
    MsgBox "Success"
 
jal said:
This also worked (this time I insert into the first db):

Code: 
 Dim cn As New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;  Jet OLEDB:Database Password=access;Data Source=C:\Names1.mdb"
    cn.Open
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = cn
   cmd.CommandText = "INSERT INTO  [Names] SELECT * FROM  [;Database=C:\Names2.mdb;  pwd =access].[Names]  "
    cmd.Execute
    cn.Close
    MsgBox "Success"
Click to expand...


jal - thank you for putting so much effort into this!

I couldn't get this to work for me, could you please post a basic structure of the two databases you made? Perhaps I'm not getting the structures correct.

e.g. DB1 Name = Names1
DB1 Table Name = Names1
DB1 Field1 = Names1_ID - autonumber
DB1 Field2 = Names1_Name - text

DB2 Name = Names2
DB2 Table Name = Names2
DB2 Field1 = Names2_ID - autonumber
DB2 Field2 = Names2_Name - text
 
Was lying in bed last night and it occured to me that if you are using a third database to get it to copy a record from mdb A to Mdb B then why not link the tables from A & B to C and run the insert from C then you have no need to worry about passwords.
 
DCrake said:
Was lying in bed last night and it occured to me that if you are using a third database to get it to copy a record from mdb A to Mdb B then why not link the tables from A & B to C and run the insert from C then you have no need to worry about passwords.
Click to expand...


Thanks David, but I don't believe that will work for our situation. The databases and interface are all on a shared team folder and everyone on the team would have the ability to open the databases. We need to password protect the databases for security, we can't afford to have any of the data changed or deleted.

Is there a way of getting direct support from Microsoft at a reasonable cost on a per issue basis?
 
jrdnoland said:
jal - thank you for putting so much effort into this!

I couldn't get this to work for me, could you please post a basic structure of the two databases you made? Perhaps I'm not getting the structures correct.

e.g. DB1 Name = Names1
DB1 Table Name = Names1
DB1 Field1 = Names1_ID - autonumber
DB1 Field2 = Names1_Name - text

DB2 Name = Names2
DB2 Table Name = Names2
DB2 Field1 = Names2_ID - autonumber
DB2 Field2 = Names2_Name - text
Click to expand...
.

I'm at work (the data is at home) but in both DBs the tables were identical (same names). "Names" is a reserved word, I guess, so maybe you can call it AllNames, like this (there was no autonum or pk col) - just one col called Name.

e.g. DB1 Name = Names1.mdb
DB1 Table Name = AllNames
DB1 Field1 = Name - text

DB2 Name = Names2.mdb
DB2 Table Name = AllNames
DB2 Field1 = Name - Text
 
Just an FYI but you don't need ADO to do this. You should be able to use your INSERT query allong with

CurrentDb.Execute "sql string here"

which would do it all via DAO instead.
 
boblarson said:
Just an FYI but you don't need ADO to do this. You should be able to use your INSERT query allong with

CurrentDb.Execute "sql string here"

which would do it all via DAO instead.
Click to expand...

Thanks Bob, in a case like what you're suggesting would the database you want to insert into be the current DB? Still how would you specify the passwords.

I'm going to retry jal's suggestion and then perhaps bit the bullet and call Microsoft. $$$
 
Okay, I know I've seen how that is done before but I can't find it. I did do this test and it worked fine for me (as an example).
Code: 
Dim strSQL As String
Dim db As DAO.Database
Dim strCurDb As String
 
strCurDb = CurrentProject.FullName
 
Set db = OpenDatabase("C:\Temp\PWDProtectedDb.mdb", False, False, "MS Access; pwd=1234")
 
strSQL = "SELECT Employees2.LastName, Employees2.FirstName INTO NewTable " & _
                    "FROM " & strCurDb & ".Employees2;"

db.Execute strSQL, dbFailOnError
db.Close
 
Set db = Nothing
 
You must log in or register to reply here.

Similar threads

Frothingslosh
  • Locked
Import Excel Data into Access via VBA
Replies
8
Views
67,232
Frothingslosh
Frothingslosh

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom