Vbscript to copy MS Access tables from another database

[Stable]

Hi there,

In the live environment an application has been developed
in MS Access with the data split and held in a separate
back end database. At the end of each month a copy of the
database is burnt onto CD for the directors. At this
stage a copy of the live Database is made and the data is
imported back into the copy before the CD is burnt.

I have used the TransferDatabase method to copy tables
from another database (same result as file /get external
data/import) into the current database.

However I would like to perform this task in a vbscript
as I have set up a vbscript to make a copy of the live
application and would like the user to be able to run the
vbscript that will make a copy of the mdb file and copy
the data tables over (the import step). The
TransferDatabase method works fine if it is run from the
command button onclick event on a form. I would like to
incorporate this step into the vbscript and don't know
how to code it.

Below is the VBA code where I delete the linked tables
and copy over the live data tables:

Private Sub cmdImportTables_Click()
On Error GoTo Err_cmdImportTables_Click

Dim TblName As Variant
Dim intTblTotal As Integer
Dim intI As Integer

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''
' This should only be used on a copy of the live system.
' Linked tables are deleted before the live tables are
imported.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''
'set up an array containing names of all linked tables
TblName = Array("AccountTypeTbl", "ActualsTbl", _
"BalanceSheetStructureTbl", "BudgetTbl
", _
"CategoriesTbl", "COAReportGroupsTbl",
"COATbl", _
"DateTbl", "EquityTbl", _
"ForecastTbl", "GraphsTbl", "ImportAct
ualsTbl", _
"ImportBudgetTbl", "ImportForecastTbl"
, _
"OrganisationDetailsTbl", "PeriodTbl",
"ProfitAndLossStructureTbl", _
"ReportCategoriesTbl", "ReportGroupsTb
l", "ReportsTbl", _
"RollingCodeTbl", "SetupTbl", _
"VariablesTbl")

intTblTotal = UBound(TblName)
Debug.Print "No. of tables = " & intTblTotal

' Delete all linked tables from this database
For intI = 1 To intTblTotal
DoCmd.DeleteObject acTable, TblName(intI)
Debug.Print intI; " = "; TblName(intI)
Next intI

Debug.Print
' Import all live tables from Data Database into this
database
For intI = 1 To intTblTotal
DoCmd.TransferDatabase acImport, _
"Microsoft Access", _
"C:\Test Data.mdb", _
acTable, _
TblName(intI), _
TblName(intI)
Debug.Print intI; " = "; TblName(intI)
Next intI

MsgBox "Linked tables deleted and Live tables
imported sucessfully."

Exit_cmdImportTables_Click:
Exit Sub

Err_cmdImportTables_Click:
MsgBox Err.Description
Resume Exit_cmdImportTables_Click

End Sub

Does anyone have any ideas?

Any help would be much appreciated!!

Cheers
Stable

[Dave Patrick]

You might try something like putting your cmdImportTables_Click code within
a module then call it something like this.

Dim appAccess, strConPathToDB
strConPathToDB = "D:\data\Access\db1.mdb"
Set appAccess = CreateObject("Access.Application.10")
appAccess.OpenCurrentDatabase strConPathToDB
appAccess.DoCmd.OpenModule "Module1", "SomeProc"
appAccess.Quit

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft MVP [Windows NT/2000 Operating Systems]
Microsoft Certified Professional [Windows 2000]
http://www.microsoft.com/protect

[Viatcheslav V. Vassiliev]

This code will not need MS Access installed:

Dim dbSrc, dbDst, TblName, conn, intTblTotal, intl
dbDst = "C:\Temp\dst.mdb" 'Database to import to
dbSrc = "C:\Temp\src.mdb" 'Database to export from
TblName = Array(...)

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbName

On Error Resume Next

intTblTotal = UBound(TblName)

' Delete all linked tables from this database

For intI = 0 To intTblTotal-1 '!
conn.Execute "DROP TABLE [" & TblName(intl) & "]"
Next

' Import all live tables from Data Database into this database

For intI = 0 To intTblTotal-1 '!
conn.Execute "SELECT * INTO [" & TblName(intl) & "] FROM [" & TblName & _
"] IN """ & dbSrc & """"
Next

But may be would be better to just copy source database into new location.

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE

"Stable" <anon...@discussions.microsoft.com> ???????/???????? ? ????????
?????????: news:ef2a01c3f1dc$5eade660$a101...@phx.gbl...

[Stable]

Hi Dave,
Thanks for your help.
I've included your code in my script but unfortunately
although the script opens the module it does not execute
the code in the procedure. Can you tell me how to
actually run this procedure from the VBscript?

Thanks again.

>.
>

[Stable]

Hi Viatcheslav,

Thanks for your help.

The DROP Table part works fine but the importing of
tables from the other database does work. I've displayed
the SQL for this and it does not include the database to
export from. From this I assume that the the 3 double
quote marks causes the remainder of the concatenation to
be ignored. Do you have any other ideas?

Thanks again

>> "BalanceSheetStructureTbl", "Budget
Tbl
>> ", _
>> "CategoriesTbl", "COAReportGroupsTb

l",
>> "COATbl", _
>> "DateTbl", "EquityTbl", _
>> "ForecastTbl", "GraphsTbl", "Import
Act
>> ualsTbl", _

>> "ImportBudgetTbl", "ImportForecastT
bl"
>> , _
>> "OrganisationDetailsTbl", "PeriodTb

>.
>

[Dave Patrick]

Probably should have been;

Dim appAccess, strConPathToDB
strConPathToDB = "D:\data\Access\db1.mdb"
Set appAccess = CreateObject("Access.Application.10")
appAccess.OpenCurrentDatabase strConPathToDB
appAccess.DoCmd.OpenModule "Module1", "SomeProc"

appAccess.DoCmd.RunCommand 181 'acCmdRun

[Viatcheslav V. Vassiliev]

Here is the sample. Query looks like:

SELECT * INTO [SrcTable] FROM [DstTable] IN "C:\DstDb.mdb"

'---------------------------------------------------------------------------
------------
Dim connStr, conn, cat, i

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb"

Set cat = CreateObject("ADOX.Catalog")
cat.Create(connStr)
Set cat = Nothing

Set conn = CreateObject("ADODB.Connection")

conn.Open connStr

conn.Execute "CREATE TABLE [Test] ([Col1] AutoIncrement, [Col2]
VarChar(10))"

For i=1 To 100
conn.Execute "INSERT INTO [Test]([Col2]) VALUES ('Row #" & i & "')"
Next

conn.Close
Set conn = Nothing

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db2.mdb"

Set cat = CreateObject("ADOX.Catalog")
cat.Create(connStr)
Set cat = Nothing

Set conn = CreateObject("ADODB.Connection")

conn.Open connStr

conn.Execute "SELECT * INTO [Test] FROM [Test] IN ""db1.mdb"""

conn.Close
Set conn = Nothing

//------------------------------------
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE

"Stable" <anon...@discussions.microsoft.com> ???????/???????? ? ????????

?????????: news:1020101c3f416$74ca78f0$a101...@phx.gbl...

[anon...@discussions.microsoft.com]

Hi Dave,

Thanks for your quick response.

I inserted the line with the RunCommand and it made no
difference - i.e. the procedure did not run. By the way
how did you arrive at the value of 181 for acCmdRun?

Cheers

>.
>

[anon...@discussions.microsoft.com]

Hi Viatcheslav,

Thanks for your reply.

Yes, I've got it working now - silly mistake I had the
incorrect variable name for the exporting from database!!

Once again thanks for your help and patience.

Cheers

>.
>

[Dave Patrick]

http://fox.wikis.com/wc.dll?Wiki~AccessConstants~VFP

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft MVP [Windows NT/2000 Operating Systems]
Microsoft Certified Professional [Windows 2000]
http://www.microsoft.com/protect

<anon...@discussions.microsoft.com> wrote:
| Hi Dave,
|

[Stable]

Hi Dave,

Thanks very much for your help

Cheers
Stable

>.
>