Password protected database 1

[Swi]

Below is my code. How do I pass the password parameter to the database when trying to append a text file to a database?

Thank you.

Private Sub AddCSVDataToNewAccessTable(FilePath As String, MDBName As String, TxtFileName As String, TableName As String)
    Dim sConTxt As String
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    sConTxt = "Driver={Microsoft Text Driver (*.txt; *.csv)}; "
    sConTxt = sConTxt & "DEFAULTDIR=" & FilePath & "; "
    sConTxt = sConTxt & "Persist Security Info=False"
    cnn.Open sConTxt
    rs.Open "SELECT * INTO [" & TableName & "] IN '" & FilePath & MDBName & _
    "' FROM [" & TxtFileName & "]", cnn
End Sub
Private Sub Command1_Click()
    AddCSVDataToNewAccessTable "C:\VBFiles\Barcode Reader\", "Mail.mdb", "20100809.txt", "Master"
    MsgBox "Done!", vbInformation
End Sub

Swi

 

[Andrzejek]

Have you tried:

sConTxt = "Driver={Microsoft Text Driver (*.txt; *.csv)}; "
sConTxt = sConTxt & "DEFAULTDIR=" & FilePath & "; "
sConTxt = sConTxt & "Persist Security Info=False[blue];"
sConTxt = sConTxt & "UID=John:PWD=MyPassword"[/blue]

Have fun.

---- Andy

 

[Swi]

Thanks. However, now I get the error Format of the initialization string does not conform to the OLE DB specification. Any ideas?

Thanks.

Swi

 

[Swi]

Actually, I missed your semicolon on the previous line. Although, it now says that it is an invalid password but it is not because I can open the Access database itself with the password in Access.

Swi

 

[SkipVought]

SEMICOLONs: [red]2 of them[/red]...

sConTxt = sConTxt & "UID=John[b][red];[/red][/b]PWD=MyPassword[b][red];[/red][/b]"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Swi]

It still says that there is an invalid password but in Access it works.

Private Sub AppendCSVDataToExistingAccessTable(FilePath As String, MDBName As String, TxtFileName As String, TableName As String)
    Dim sConTxt As String
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    sConTxt = "Driver={Microsoft Text Driver (*.txt; *.csv)}; "
    sConTxt = sConTxt & "DEFAULTDIR=" & FilePath & "; "
    sConTxt = sConTxt & "Persist Security Info=False;"
    sConTxt = sConTxt & "UID=Admin;PWD=XXXP@ssword1;"
    cnn.Open sConTxt
    rs.Open "INSERT INTO [" & TableName & "] ([imp_code], [claimant_idn], [encounter_idn]) IN '" & FilePath & MDBName & _
    "' SELECT [imp_code], [claimant_idn], [encounter_idn] FROM [" & TxtFileName & "]", cnn
End Sub

Swi

 

[SkipVought]

What would happen if ...

Debug.Print "INSERT INTO [" & TableName & "] ([imp_code], [claimant_idn], [encounter_idn]) IN '" & FilePath & MDBName & _
    "' SELECT [imp_code], [claimant_idn], [encounter_idn] FROM [" & TxtFileName & "]"

and then COPY that result from the Immediate Window and RUN that SQL in your database?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I prefer to code something like this...

    s = "INSERT INTO [" & TableName & "]"
    s = s & " ([imp_code]"
    s = s & ", [claimant_idn]"
    s = s & ", [encounter_idn]) "
    s = s & "IN '" & FilePath & MDBName & "' "
    s = s & "SELECT"
    s = s & "  [imp_code]"
    s = s & ", [claimant_idn]"
    s = s & ", [encounter_idn] "
    s = s & "FROM [" & TxtFileName & "]"
    
    Debug.Print s
    
    rs.Open s, cnn, adOpenStatic, adLockUnspecified, adCmdText

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Swi]

Thanks for the tip although everthing works just fine if I take the password off of the database. If I try password protecting the database I get the error:

Run-time error '-2147217911 (80040e09)';
[Microsoft][ODBC Text Driver] Not a valid password.

Swi

 

[SkipVought]

Did you check ADO Help?

You open the recordset with the DEFAULT LockType which is adLockReadOnly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Swi]

I have tried different lock types but to no avail. I have also looked in the ADO help but could not find anything to solve the problem. Thanks.

Swi

 

[vb5prgrmr]

Ahhh... I think the problem is, is that someone did not realize the OP is trying to password protect a CSV file or a text file through an ADO ODBC DNS Less connection string...

But if there is a way, see

http://www.connectionstrings.com

Good Luck

 

[dilettante]

You have a lot going on here that is odd.

For one thing you're taking a sort of "inside out" approach using the thunk layer "OLEDB Provider for ODBC" (MSDASQL, which you are using implicitly).

Turn it around and open the Jet MDB using the Jet 4.0 Provider which can read and write text files via its Text IISAM instead. This will give you a way to specify Jet connection parameters more easily, and is probably a lot more efficient than thunking into two ODBC drivers.

Then there is the issue of Jet security. Jet offers two aproaches, a database password approach (share-level security) and an internal security model (user-level or workgroup security). Most people just use the simpler model where there is just a global database level password and a default user Admin with a blank/NULL password.

Then for some bizarre reason you're using an ADO Recordset here. A query such as a SELECT ... INTO is an action query that doesn't return a rowset. A more appropriate approach for these is to use the Execute method on a Connection or Command object.


Here is an example that creates an empty database named Books.mdb with a database password of "Rosebud" and then imports a CSV named Books.txt into a new table Books:

Books.txt

Title,Author,ISBN13,Pages
1984,George Orwell,978-0451524935,268
Animal Farm,George Orwell,978-0451526342,144
Brave New World,Aldous Huxley,978-0060929879,288
Fahrenheit 451,Ray Bradbury,978-0345342966,208
Jane Eyre,Charlotte Brontë,978-0142437209,532
Wuthering Heights,Emily Brontë,978-0141439556,416
Agnes Grey,Anne Brontë,978-1593083236,256
Walden,Henry David Thoreau,978-1420922615,156
Walden Two,B. F. Skinner,978-0872207783,301
"Eats, Shoots & Leaves",Lynne Truss,978-1592400874,209

Form1.frm

Option Explicit

Private Sub CreateMDB( _
    ByVal PathToMDB As String, _
    ByVal MDB As String, _
    ByVal MDBPW As String)

    Dim catDB As Object 'As ADOX.Catalog.
    Set catDB = CreateObject("ADOX.Catalog")
    With catDB
        .Create "Provider=Microsoft.Jet.OLEDB.4.0;" _
              & "Jet OLEDB:Engine Type=5;" _
              & "Mode=Share Exclusive;" _
              & "Data Source='" & PathToMDB & "\" & MDB & "'"

        With .ActiveConnection
            .Execute "ALTER DATABASE PASSWORD " & MDBPW & " NULL", , _
                     adCmdText
        End With
    End With
End Sub

Private Function AddCSVAsNewTable( _
    ByVal PathToMDBAndCSV As String, _
    ByVal MDB As String, _
    ByVal MDBPW As String, _
    ByVal CSVFile As String, _
    ByVal Headers As Boolean, _
    ByVal NewTableName As String) As Long
    
    Dim cnMDB As ADODB.Connection
    
    Set cnMDB = New ADODB.Connection
    With cnMDB
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "Jet OLEDB:Database Password='" & MDBPW & "';" _
            & "Data Source='" & PathToMDBAndCSV & "\" & MDB & "'"
        
        .Execute "SELECT * INTO [" & NewTableName & "] " _
               & "FROM [Text;Database=" & PathToMDBAndCSV & ";HDR=" _
               & IIf(Headers, "Yes", "No") & "].[" & CSVFile & "]", _
                 AddCSVAsNewTable, _
                 adCmdText Or adExecuteNoRecords
        .Close
    End With
End Function

Private Sub cmdCreate_Click()
    CreateMDB App.Path, "Books.mdb", "Rosebud"
    MsgBox "Database Created!"
End Sub

Private Sub cmdImport_Click()
    MsgBox "Imported " _
         & CStr(AddCSVAsNewTable(App.Path, _
                                 "Books.mdb", _
                                 "Rosebud", _
                                 "Books.txt", _
                                 True, _
                                 "Books")) _
         & " rows"
End Sub

But this really isn't a VB question and probably belongs in forum701 instead.

 

[Swi]

Thanks. I will look into it further. I ended up with something like that.

Private Sub AppendTextDataToExistingAccessTable(DataPath As String, MDBName As String, TxtFileName As String, TableName As String)
    ' Create schema file for pipe delimited file with current text file name
    Open DataPath & "schema.ini" For Output As #1
    Print #1, "[" & TxtFileName & "]"
    Print #1, "Format=Delimited(|)"
    Print #1, "ColNameHeader = True"
    Print #1, "MaxScanRows = 0"
    Print #1, "CharacterSet = ANSI"
    Close #1
    ' Inserts data into Access 2000 table
    Dim sConTxt As String
    Dim cnn As New ADODB.Connection
    sConTxt = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataPath & ";Extended Properties=""text;HDR=Yes;IMEX=1;FMT=Delimited(|);"";"
    cnn.Open sConTxt
    cnn.Execute "INSERT INTO [" & TableName & "] ([imp_code], [claimant_idn], [encounter_idn], [mode_idn], " & _
    "[entity_type_idn], [mailing_type_idn], [date_mailed], [note_text], [mailing_request_idn], " & _
    "[disease_id], [disease_desc], [mailing_desc], [accountcode], [client_idn], [account], [analyst_name], " & _
    "[rundate], [member_last_name], [member_first_name], [member_address1], [member_address2], [member_city], " & _
    "[member_state], [member_zip], [member_age], [benefitregion], [member_ext_id], [risklevel], [educational], " & _
    "[health_coach], [health_coach_extension], [Effective_Date], [PCP_name], [PCP_address1], [PCP_address2], " & _
    "[PCP_city], [PCP_state], [PCP_zip], [PCP_phone]" & _
    ") IN '" & DataPath & MDBName & "' SELECT [imp_code], [claimant_idn], [encounter_idn], [mode_idn], " & _
    "[entity_type_idn], [mailing_type_idn], [date_mailed], [note_text], [mailing_request_idn], " & _
    "[disease_id], [disease_desc], [mailing_desc], [accountcode], [client_idn], [account], [analyst_name], " & _
    "[rundate], [member_last_name], [member_first_name], [member_address1], [member_address2], [member_city], " & _
    "[member_state], [member_zip], [member_age], [benefitregion], [member_ext_id], [risklevel], [educational], " & _
    "[health_coach], [health_coach_extension], [Effective_Date], [PCP_name], [PCP_address1], [PCP_address2], " & _
    "[PCP_city], [PCP_state], [PCP_zip], [PCP_phone] FROM [" & TxtFileName & "]", NumOfRecsImp, adCmdText Or adExecuteNoRecords
    cnn.Close
    Set cnn = Nothing
End Sub

Swi

 

[Swi]

How would your solution be implemented if I want to add to an existing database?

Thanks.

Swi

 

[Swi]

Got it. Thanks for all of your help. I had to make a schema files first because the files are pipe delimited. Otherwise I was getting an error on import into the database.

Private Sub AppendTextDataToExistingAccessTable(ByVal PathToMDBAndTXT As String, ByVal MDB As String, _
ByVal MDBPW As String, ByVal TXTFile As String, ByVal Headers As Boolean, ByVal TableName As String)

    ' Create schema file for pipe delimited file with current text file name
    Open PathToMDBAndTXT & "\schema.ini" For Output As #1
    Print #1, "[" & TXTFile & "]"
    Print #1, "Format=Delimited(|)"
    Print #1, "ColNameHeader = True"
    Print #1, "MaxScanRows = 0"
    Print #1, "CharacterSet = ANSI"
    Close #1
    Dim cnMDB As ADODB.Connection
    Set cnMDB = New ADODB.Connection
    With cnMDB
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Jet OLEDB:Database Password='" & MDBPW & "';" _
        & "Data Source='" & PathToMDBAndTXT & "\" & MDB & "'"
        .Execute "INSERT INTO [" & TableName & "] ([imp_code], [claimant_idn], [encounter_idn], [mode_idn], " & _
        "[entity_type_idn], [mailing_type_idn], [date_mailed], [note_text], [mailing_request_idn], " & _
        "[disease_id], [disease_desc], [mailing_desc], [accountcode], [client_idn], [account], [analyst_name], " & _
        "[rundate], [member_last_name], [member_first_name], [member_address1], [member_address2], [member_city], " & _
        "[member_state], [member_zip], [member_age], [benefitregion], [member_ext_id], [risklevel], [educational], " & _
        "[health_coach], [health_coach_extension], [Effective_Date], [PCP_name], [PCP_address1], [PCP_address2], " & _
        "[PCP_city], [PCP_state], [PCP_zip], [PCP_phone]" & _
        ") IN '" & PathToMDBAndTXT & "\" & MDB & "' SELECT [imp_code], [claimant_idn], [encounter_idn], [mode_idn], " & _
        "[entity_type_idn], [mailing_type_idn], [date_mailed], [note_text], [mailing_request_idn], " & _
        "[disease_id], [disease_desc], [mailing_desc], [accountcode], [client_idn], [account], [analyst_name], " & _
        "[rundate], [member_last_name], [member_first_name], [member_address1], [member_address2], [member_city], " & _
        "[member_state], [member_zip], [member_age], [benefitregion], [member_ext_id], [risklevel], [educational], " & _
        "[health_coach], [health_coach_extension], [Effective_Date], [PCP_name], [PCP_address1], [PCP_address2], " & _
        "[PCP_city], [PCP_state], [PCP_zip], [PCP_phone] FROM [Text;Database=" & PathToMDBAndTXT & ";HDR=" & _
        IIf(Headers, "YES", "NO") & "].[" & TXTFile & "]", NumOfRecsImp, adCmdText Or adExecuteNoRecords
    .Close
    End With
End Sub

Swi