Using Access SQL to retrieve info from a protected database 1

[CoffeeNow]

Is it possible to modify an SQL statement that will allow a make table query in the current database to retrieve data from a password protected database and then export the results to the current database?

I've tried running the make table query and Access tells me that the password is invalid, although it does not give me the opportunity to enter the password.

Bottom line: what is the verbage (if any exists) I should use that would allow me to enter the password in the make table SQL statement?

(Access97)

 

[tomschafer]

You would need to enter the password in the connect string property of the SQL.

 

[DJN]

Here is a function to return the password.


David
'--------------------------------------------------
' basDatabasePassword
'
' Get the database password of a Jet 3.0/3.5 database.
'
' --------------------------
' This code is provided for the express purpose of destroying the burgeoning
' shareware industry of Jet 3.x/3.5x database password crackers. You should
' keep in mind that it is still quite illegal to break into a database you have
' no authorization to view. Please don't do anything that would cause me to
' have less respect for you than for the lifeless souls who try to charge money
' for code of this nature on a "per database" basis.
'
' TO USE:
' 1) Create a new module in any VBA host like Access, Excel, or Visual Basic
' 2) Hit <Ctrl+G> to get to the debug window
' 3) Run the following line of code in the debug window (replacing c:\foo.mdb with
' the full path/name to your database:
'
' ? StPasswordOfStDatabase(&quot;c:\foo.mdb&quot
'
' The function will return the Database Password to you.
'
' --------------------------
' Sample code to allow you to prove to yourself that the function
' works (the following code can be run from the debug window).
' Change the password from 01234567890123456789 to any
' arbitrary value 1-20 characters in length:
'
' Set dbe = CreateObject(&quot;dao.dbengine.35&quot
' Set db = dbe.CreateDatabase(&quot;c:\temp35.mdb&quot;, &quot;;LANGID=0x0409;CP=1252;COUNTRY=0&quot
' db.NewPassword &quot;&quot;, &quot;01234567890123456789&quot;
' db.Close: Set db = Nothing: Set dbe = Nothing
' Debug.Print StPasswordOfStDatabase(&quot;c:\temp35.mdb&quot
' --------------------------
'
' (c) 1998 Trigeminal Software, Inc. All Rights Reserved
'--------------------------------------------------
'Option Compare Binary

Public Function StPasswordOfStDatabase(stDatabase As String) As String
Dim hFile As Integer
Dim ich As Integer
Dim stBuffer As String
Dim rgbytRaw() As Byte
Dim rgbytPassword() As Byte
Dim rgbytNoPassword() As Byte

' Create the byte array with the 20 bytes that are present when there
' is no database password
rgbytNoPassword = ChrB(134) & ChrB(251) & ChrB(236) & ChrB(55) & ChrB(93) & _
ChrB(68) & ChrB(156) & ChrB(250) & ChrB(198) & ChrB(94) & _
ChrB(40) & ChrB(230) & ChrB(19) & ChrB(182) & ChrB(138) & _
ChrB(96) & ChrB(84) & ChrB(148) & ChrB(123) & ChrB(54)

' Grab the 20 bytes from the real file whose password
' we are supposed to retrieve
hFile = FreeFile
Open stDatabase For Binary As #hFile
Seek #hFile, 66 + 1
rgbytRaw = InputB(20, #hFile)
Close #hFile

' Enough prep, lets get the password now.
ReDim rgbytPassword(0 To 19)
For ich = 0 To 19
rgbytPassword(ich) = rgbytRaw(ich) Xor rgbytNoPassword(ich)
Next ich

' Add a trailing Null so one will always be found, even if the password is 20
' characters. Then grab up to the first null we find and return the password
stBuffer = StrConv(rgbytPassword, vbUnicode) & vbNullChar
StPasswordOfStDatabase = Left$(stBuffer, InStr(1, stBuffer, vbNullChar, vbBinaryCompare) - 1)
End Function

 

[tomschafer]

Very cool. Have a star.

 

[CoffeeNow]

Thanks for the help, but:

Pez, I don't know the proper syntax in order to enter the password in the SQL statement of Access.

DJN, I already know the password for the database. i need to be able retrieve the results of a make table query from the protected database.

I appreciate your help!

Thanks again!

 

[tomschafer]

I was wrong- you can only enter the password in an odbc connect string. The only solution I see for you is to link the tables.

 

[CoffeeNow]

I created the following work around:

First - I copied the Make Table Query from the Password Protected Database to the New Database.

Second - I added the following SQL line to the copied Make Table Query : IN c:\temp\passwordprotecteddatabase.mdb

Third - I added a button to the New Database witht the following VB code:

Private Sub Command22_Click()

Dim wrk As Workspace
Dim dbProtected As Database
Dim objAccess As Access.Application


Set wrk = DBEngine.Workspaces(0)
Set dbProtected = wrk.OpenDatabase(&quot;c:\temp\passwordprotecteddatabase.mdb&quot;, _
False, False, &quot;;PWD=password&quot
Dim stDocName As String

stDocName = &quot;Make Table Query&quot;
DoCmd.OpenQuery stDocName, acViewNormal, acEdit

End Sub