Link to home
Start Free TrialLog in
Avatar of CRMEHC
CRMEHC

asked on

Use VBA to Import Table into Access from Password-Protected Access Database without Record Set

I would like to import data from a password-protexted MS Access datbase into another MS Access datbase, but I don't want to put the data into a record set, I just want to do a plain import.

This is my original code:

'       import new Questions table
'       -------------------------------------------------------
        DoCmd.TransferDatabase acImport, "Microsoft Access", _
            strPthQdb, acTable, "Questions", _
            "Questions"

The variable strPthQdbis the path to the Questions database, of which there are several. Most don't have passwords, but a few do and they're giving me a headache.

I have saved the database passwords to a local table and can easily retrieve it and assign it to the variable 'strPW'.

How can I integrate easily integrate 'strPW' into my VBA without doing back flips? Every solution I've found online seems to be much more complex and convoluted than seems necessary.

Thanks ahead of time for a solution!
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<' next line doesn't seem to work
    CurrentDb.TableDefs.Refresh>

Pete,
Might this work?:
Application.RefreshDatabaseWindow


...just passing through...

;-)

Jeff
That sounds promising Jeff.
Avatar of CRMEHC
CRMEHC

ASKER

Thanks, peter51r. So far, so good.  I  ran into the problem of the tables not refreshing, and unfortunately Jeff's (boag2000) suggestion 'Application.RefreshDatabaseWindow' isn't working, either.

Any other suggestions? I can use the newly imported tables unless I stop process and begin a new one, which I definitely don't want to do.

Frank
It appears to be a timing issue.

If i do this after the import the tables become visible....

    Dim tim
    tim = Timer
    Do Until Timer > tim + 5    'wait 5 seconds
    DoEvents
    Loop
    Application.RefreshDatabaseWindow
Avatar of CRMEHC

ASKER

Hello peter57r,

Not sure where to put your timer code. I only want to import 2 tables, so my Do statement states:

Do Until (i = 2)

I could put the 'Do Until Timer > tim + 5' statement right before 'Application.RefreshDatabaseWindow' but I'm afraid I'll end up in an endless loop.

I'll test it. In the meantime, if you have any suggesstions...

Thanks.
Avatar of CRMEHC

ASKER

peter57r -

Yeah. I'm lost.
Avatar of CRMEHC

ASKER

peters57r-

I figured it out.  here's the end of my code now:

  '   WAIT 5 SECONDS!!!
  '   -------------------------------------------------------
      Do Until Timer > (tim + 5)    'wait 5 seconds
  'Debug.Print tim
      Loop

  '   close temp database, clear object variable
  '   -------------------------------------------------------
      appacc.CloseCurrentDatabase
      Set appacc = Nothing

  '   refresh tables
  '   -------------------------------------------------------
      Application.RefreshDatabaseWindow

I don't think the program even waits 5 seconds, but it's long enough for the tables to refresh and continue executing the rest of the code.

Thanks!

Frank
Avatar of CRMEHC

ASKER

Anyone using this solution, please read follow-up comments to see how to insert a 5-second delay to allow the newly imported tables to refresh.