Export to Remote DB VBA to OpenPasswordProtectedDB

[paul.holness]

Hi, Below is the code to export to the BE. There are 32 tbls! How do I program using vbs to bypass or enter the password automatically?

tbl_CA1InputNFileJanMar = "CA8InputFile_January_March_2013"
tbl_CA1InputNFileAprJun = "CA8InputFile_April_June_2013"
tbl_CA1InputNFileJulSep = "CA8InputFile_July_September_2013"
tbl_CA1InputNFileOctDec = "CA8InputFile_October_December_2013"
DoCmd.TransferDatabase transfertype:=acExport, _
databasetype:="Microsoft Access", _
databasename:="Z:\Turnaround Integrated Controls\1.4.12_Cost Control\Cost Performance Report\2013 Cost Performance Report\Performance Database\Data Database\TA2013_PR_TrackingDB.accdb", _


ObjectType:=acTable, Source:="" & tbl_CA1InputNFileJanMar & "", _
Destination:="" & tbl_CA1InputNFileJanMar & "", structureonly:=True

Here I found the Compact and Repair script using the feature I want, but do n ot know how to incorporate it:


Sub OpenPasswordProtectedDB()
'Define as Static so the instance of Access
'doesn't close when the procedure ends.
Static acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
strDbName = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=nwind")
acc.OpenCurrentDatabase strDbName
db.Close
Set db = Nothing

End Sub

Here is another example I found:

1. Public Function fODBCTablesRefresh() As Boolean
2. ' This function refreshes the DAO ODBC table connections
3. ' listed in the qryODBCRefreshList query
4. ' which in turn lists the first table to be connected for each different
5. ' connection string listed in table tblDatabasesConnected.
6. '
7. ' This function is used to automate the initial refresh of the ODBC table connection
8. ' so that updates etc can run without user intervention to supply the UID and PWD components
9. ' of the DB connection.
10. '
11. ' 23/4/09
12. '
13. On Error GoTo refresh_err
14. Dim daoDB As DAO.Database
15. Dim tdef As DAO.TableDef
16. Dim RS As DAO.Recordset
17. Dim strConnectOld As String
18. Set daoDB = CurrentDb
19. Set RS = CurrentDb.OpenRecordset("qryODBCRefreshList")
20. Do While Not RS.EOF
21. Set tdef = daoDB.TableDefs(RS![tablename])
22. strConnectOld = RS![Connect String]
23. tdef.Connect = strConnectOld & "; UID=" & RS![USER ID] & "; PWD=" & RS![CONNECTION PASSWORD]
24. tdef.RefreshLink
25. tdef.Connect = strConnectOld 'restore no-password version of connect string
26. RS.MoveNext
27. Loop
28. RS.Close
29. fODBCTablesRefresh = True
30. Exit Function
31. refresh_err:
32. fODBCTablesRefresh = False
33. End Function

[NTC]

Export to Remote DB ?? what is meant by that ?? either a db is linked or it is not - and if it is not there is no exporting to it.....and if it is linked there is no need to export.... am greatly confused by what it is that you are attempting to achieve.....