Pre Specifying Password when importing tables 1

[khwaja]

I use the following code to import a table from a password protected database. Is there any way I can pre specify the password in the code so that it does not prompt user for the password?

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"H:\PNP\Strategic Plan.mdb", , "tblMaster", "tblMaster"

Did not have much luck with the help.

Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

If the path name to the password-protected database is static, as your example implies, you could create a link to the table. At the time you create the link, you are prompted for the password. Access then stores the password within the link definition so it won't have to be entered later.

Warning: The stored password will be unencrypted, and could be seen by anybody who can open the link in Design View.

Instead of using TransferDatabase, you can then access the linked table directly, or if you prefer, use a MakeTable query to create a local duplicate table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks Rick. It was a link table previously but I had to sever the link as it was holding up my work on the other database. Is it fait to assume, based on your response, that there is no option to include the password in the code when transferring the table?


Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Well, not that I could find in the Help file. Although you could if you could access the table using ODBC. I don't think Access allows you to access Jet databases using ODBC, though.

If there's a way to do it, it could be by changing your file spec to something along the lines of:
"H:\PNP\Strategic Plan.mdb [MS Access;PWD=password]"
or maybe leave the "MS Access" part of that out

Another approach you might investigate is running a SELECT SQL statement that specifies the IN keyword for the source table. There's an example for dBASE IV in the Help file topic for the SourceConnectStr property. Since the syntax "[dBASE IV;]" resembles part of the Connect property used for dBASE IV, it makes me think you might be able to use other Connect property keywords, such as:
SELECT *
FROM tblMaster
IN 'H:\PNP\Strategic Plan.mdb'[MS Access;PWD=password]
INTO tblNewMaster
This idea comes from looking at the Connect property of a table linked into a password-protected database. (In the Debug Window, execute ?CurrentDb.TableDefs("tblname&quot.Connect).

If you want to do more Help file searching, try doing a Find search (as opposed to looking in the index) for the word PWD. It occurs in many topics, and I didn't read them all.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks for the tip. There is some promising development as I managed to get the following to work but only to the point that it prompts me to select a database which it shouldn't as I have already indicated one.

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=MS Access 97 Database; ;PWD=grak;;" _
& "DATABASE=H:\PNP\Strategic Plan.mdb", acTable, "Master", "Master"

I am reproducing the full syntax for your convenience:

DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=

http://www;LANGUAGE=us_english;"

_
& "DATABASE=pubs", acTable, "Authors", "dboAuthors"

Ar you able to tweak this to get this work without any prompts?


Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Actually, I've never worked with ODBC, so I can't really experiment with it. However, I notice you have removed some of the keywords and left the semicolons following them. I'm pretty sure you need to remove the redundant semicolons.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

I have removed semi colons but the result is not different. I am still not able to resolve this but want to thank you for taking the time to look at this. If you come across any solution, please let me know.



Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Thanks for the star.

Now that we've put a bunch of replies on this thread, some of the more knowledgeable folks will probably skip over it, thinking the question has been exhausted already. You might want to create a new thread to see if anybody else has any ideas.

Sorry I couldn't help you out.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[LittleSmudge]

You're quite right Rick, Access won't let you ODBC to another Access database.


Also, on the general issue of ODBDC connection strings - double semicolons is not good, so if you omit a key word then get rid of the semicolon too.


Other than making the Link when you need it and deleting it when finished - I can't think of an alternative either. HOWEVER, I can't see why a link, established in this database looking at a table in another database will stop any work at all in the other database.

I have many 'FrontEnd' databases that are linked to 'BackEnd' databases. I have never experienced any problems making changes in a BackEnd when there are links to it. So what do you mean AK by
It was a link table previously but I had to sever the link as it was holding up my work on the other database.






G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[khwaja]

Thanks for the reply.

Mine is not a split database and probably that might be the reason that when this db is being used, I am unable to make any structural changes in the database it is linked to. By structural changes, I mean changes in the table attributes etc. This does not happen often but since systems are still being improved, it does cause me inconvenience. That was the prime reason for me to look for such altrernatives as importing the table. But managing password is not easy as I don't want users to issue passwords when the code runs each week to update the table. I hope this answers your questions. I tried splitting the db but I had a lot of issues with switcboard. So reverted back.



Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

"...when the code runs each week to update the table."

If the code only runs once a week, and you don't touch the table other than that, then I'd be surprised if the existence of the link were causing your problem. I believe the mere existence of the link will not cause a connection to be built when your database is opened.

If you do open the table, a connection is built, but it should be released when the table is closed. (In fact, there is an optimization technique for networked databases that involves opening a hidden recordset on a remote table, for the very purpose of keeping the connection open.)

At the very least, if you don't want to built a permanent link in the database, I should think you could build one with DAO on the fly, including the password in the Connect string, and then delete it when you're done. (Remember that the password will be exposed in your code and in the link's properties, though not in the Access property sheet.)

Note: The documentation on the Connect property setting is at best confusing and incomplete, and may actually be inaccurate. When I have built links this way, I've had trouble getting the Connect property right. Nevertheless, it can be done. If you have trouble with it, don't give up in frustration--come back to Tek-Tips.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks Rick. I really don't want a permanant link and this is what I used to replace pre existing arrangement. Could you please help me change this to DAO so that I use connect string? Or in other words, what changes are required to be made to this? I am not very confident when it comes to writing code.

Sub CopyPNP()
Dim dbs As Database, tdf As TableDef, strSQL As String
Set dbs = CurrentDb

'If tblTest exists, delete it.
For Each tdf In dbs.TableDefs
If tdf.Name = "Master" Then
dbs.TableDefs.Delete tdf.Name
End If
Next tdf
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"H:\PNP\Strategic Plan.mdb", , "Master", "Master"

MsgBox "Property network Plan data uppdated successfully."
Set dbs = Nothing

End Sub



Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Here you go:

Sub CopyPNP()
Const LocalTableName = "Master"
Const ExternalTableName = "Master"
Const ExternalDbPath = "H:\PNP\Strategic Plan.mdb"
Const ExternalDbPassword = &quot;password&quot;        ' <<< replace with your password

    'If tblTest exists, delete it.
    DeleteTable LocalTableName
    
    'Create link to the PNP Master table
    LinkAccessTable ExternalTableName, ExternalDbPath, LocalTableName, _
        ExternalDbPassword

    MsgBox &quot;Property network Plan data uppdated successfully.&quot;

End Sub

Public Sub DeleteTable(TableName As String)
' Deletes the specified table from the current database, if it exists.

    On Error GoTo ErrorHandler
    CurrentDb.TableDefs.Delete TableName
ErrorExit:
    Exit Sub
ErrorHandler:
    If Err.Number = 3265 Then ' Item not found in this collection
        Resume Next
    End If
    Err.Raise Err.Number
End Sub

Public Sub LinkAccessTable(TableName As String, InDatabase As String, _
                           Optional LocalName As String, _
                           Optional Password As String)
' Creates a link to a table in an external Jet database.
' If LocalName is not specified, it defaults to TableName.
' If the external database is password protected, the password must be specified
    Dim dbs As DAO.Database, tdf As DAO.TableDef
    Dim strConnect As String
    
    On Error GoTo ErrorHandler
    If Len(LocalName) = 0 Then LocalName = TableName
    strConnect = &quot;;DATABASE=&quot; & InDatabase
    If Len(Password) > 0 Then strConnect = strConnect & &quot;;PWD=&quot; & Password
    Set dbs = CurrentDb()
    Set tdf = dbs.CreateTableDef(LocalName, 0, TableName, strConnect)
    dbs.TableDefs.Append tdf
    dbs.TableDefs.Refresh
    RefreshDatabaseWindow
ErrorExit:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub
ErrorHandler:
    Set tdf = Nothing
    Set dbs = Nothing
    Err.Raise Err.Number
End Sub

When you're done with the table, you can simply call DeleteTable "Master" to delete the link again.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[khwaja]

Thanks Rick. This is a great alternative approach to the issue I had. This works great. However, if I can get rid of the link after the code has run, this would mean that I achieved the the same result as intended. I tried with the following changes whereby I renamed the LocalTable as Master1 and then tried to save it as Master and then delete the linked table. But even after making minor changes in the declaration section and adding another set of code, I still end up having a run time error 3011. Probably it is to do with linked table which when copied under normal circumstances is copied as linked table. I am sure I am not using the write code when copying a linked table. Could you have a look on CopyOld() code.

Sub CopyPNP()
Const LocalTableName = "Master1"
Const ExternalTableName = "Master"
Const ExternalDbPath = "H:\PNP\Strategic Plan.mdb"
Const ExternalDbPassword = &quot;password&quot; ' <<< replace with your password

'If tblTest exists, delete it.
DeleteTable LocalTableName

'Create link to the PNP Master table
LinkAccessTable ExternalTableName, ExternalDbPath, LocalTableName, _
ExternalDbPassword

CopyOld

MsgBox &quot;Property network Plan data uppdated successfully.&quot;

End Sub

Sub CopyOld()
Const LocalTableName = &quot;Master1&quot;
Dim dbs As Database, tdf As TableDef, strSQL As String
Set dbs = CurrentDb

'If tblTest exists, delete it.
For Each tdf In dbs.TableDefs
If tdf.Name = &quot;Master&quot; Then
dbs.TableDefs.Delete tdf.Name
End If
Next tdf

DoCmd.CopyObject , &quot;Master1&quot;, acTable, &quot;Master&quot;

DeleteTable LocalTableName

ErrorExit:

Exit Sub
ErrorHandler:
If Err.Number = 3265 Then ' Item not found in this collection
Resume Next
End If
Err.Raise Err.Number

Set dbs = Nothing

End Sub


Cheers

AK

Note: Using Access 97 - still.

 

[RickSpr]

Look at your logic. When you run CopyPNP, it deletes &quot;Master&quot; if it exists, then links &quot;Master&quot; from the external database, then calls CopyOld. CopyOld then deletes &quot;Master&quot;, then attempts to copy it to &quot;Master1&quot;, then deletes &quot;Master1. Of course you're getting error 3011, &quot;The Microsoft Jet database engine could not find the object '<name>'&quot; - you keep trying to delete it before you've even used it.

I'm surprised you didn't realize that. Apparently you're attempting to use code without trying to understand it.

Giving away whole chunks of code is frowned on by the management here. They absolutely don't want Tek-Tips to get a reputation as a free programming service. So when somebody gives you a big chunk, it's important that you study it, referring to the help file or other references, until you understand it thoroughly.

However, you're correct that CopyObject will create a copy of the link, and not a copy of the table. What I'd suggest is that you create a permanent (empty) copy of the Master table in your local database. Then modify the code to create the link when you need it (using a different local name, such as &quot;Master1&quot, run an SQL INSERT statement to copy all the rows to your local Master table, and delete the link. When you're through with the Master table, run an SQL DELETE statement to delete all the rows. You can run SQL statements using the DoCmd.RunSQL method.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein