Access 2002 runtime limitation (GetObject)

[Vic Spainhower]

Hello,

I am trying to use automation to copy some tables from the backend of an
application to a new database. However, it seems like ever method I try
fails because of various limitations. The process needs to be able to run in
a runtime environment (Access 2002) which is currently what is causing me
grief. I'm using the following function to do the copy which works fine when
full access is installed but fails in the runtime environment with error 429
ActiveX component can't create object. However, it is failing on the
GetObject which I thought was supposed to work when starting Access using
Shell.

Can someone offer a solution to this or another method to accomplish same
thing? I'm just tring to create a new database and trhen copy several tables
into it. Seems simple enough and it's driving me nuts!

Vic


Function CreateScheduleDB(strSource As String, strDestination As String) As
Integer
Dim objAccessApp As Object
Dim strCmd As String

On Error GoTo ErrorHandler
DoCmd.Hourglass True

CreateScheduleDB = False

strCmd = SysCmd(acSysCmdAccessDir) & "\MSAccess.exe " _
& Chr(34) & strSource & Chr(34) & " /wrkgrp " & DBEngine.SystemDB
_
& " /user Admin /pwd ''"

Call Shell(strCmd, vbNormal) 'Or use vbHide if you do not want to show
the database
Set objAccessApp = GetObject(strSource)
objAccessApp.DoCmd.CopyObject strDestination, , acTable, "Table1"
objAccessApp.DoCmd.CopyObject strDestination, , acTable, "Table2"
objAccessApp.DoCmd.CopyObject strDestination, , acTable, "Table3"
CreateScheduleDB = True

ExitHere:
On Error Resume Next
objAccessApp.CloseCurrentDatabase
objAccessApp.Quit
DoCmd.Hourglass False
Exit Function

ErrorHandler:
Select Case Err.Number

Case Else
MsgBox "Error Number: " & Err.Number & ", " & Err.Description,
vbCritical, "Unhandled Error"
CreateScheduleDB = False
Resume ExitHere
End Select

 

[Scott McDaniel]

check this response from Frank Miller to another, similar question:

http://groups.google.com/groups?hl=...8&oe=UTF-8&newwindow=1&safe=off&start=20&sa=N

 

[Vic Spainhower]

Scott,

Frank says that starting the runtime version of access using Shell and
specifying all of the parameters then using GetObject to retrieve the
application object will work just fine. This is what I'm doing but the
GetObject fails with a 429 error. What am I missing???

Call Shell(strCmd, vbNormal)
Set objAccessApp = GetObject(strSource)

Value of strCmd:
C:\Program Files\Microsoft Office\Office10\MSAccess.exe
"C:\Database\ShowSecXP\Showsec_Test_Database_XP.mdb" /wrkgrp
C:\Database\ShowSecXP\ShowSecWrkGrp.mdw /user Admin /pwd ''

Value of strSource:
C:\Database\ShowSecXP\Showsec_Test_Database_XP.mdb

Vic

 

[Dirk Goldgar]

Hello,

I am trying to use automation to copy some tables from the backend
of an application to a new database. However, it seems like ever
method I try fails because of various limitations. The process needs
to be able to run in a runtime environment (Access 2002) which is
currently what is causing me grief. I'm using the following function
to do the copy which works fine when full access is installed but
fails in the runtime environment with error 429 ActiveX component
can't create object. However, it is failing on the GetObject which I
thought was supposed to work when starting Access using Shell.

Can someone offer a solution to this or another method to accomplish
same thing? I'm just tring to create a new database and trhen copy
several tables into it. Seems simple enough and it's driving me nuts!

One alternative would be to forget about using automation entirely and
use DAO to copy the table definitions from one database to the other
using the CopyTableDef function posted in this MS KnowledgeBase article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;217011

Having copied the tabledefs into the new database, you could follow up
by executing append queries to insert the data from the source tables,
using the IN clause to identify the source database.

I should mention that if you don't need the new, copied tables to have
any of the indexes or extended properties of the originals, you could
just use make-table queries (with the IN clause) to create them, without
ever copying the tabledefs at all.

 

[Paul Overway]

You may need to enclose the various files in quotes....not just the database
file as you've indicated.

value of strCmd should be
"C:\Program Files\Microsoft Office\Office10\MSAccess.exe"
"C:\Database\ShowSecXP\Showsec_Test_Database_XP.mdb" /wrkgrp
"C:\Database\ShowSecXP\ShowSecWrkGrp.mdw" /user Admin /pwd

 

[Vic Spainhower]

Paul,

Thanks for the reply but unfortunately the quotes didn't help.

Vic

 

[Vic Spainhower]

Hi Dirk,

*Great Tip* Thank you very much!

The process seems a little slow but it WORKS!

I'm not certain why the automation routine doesn't work but I'm not so sure
this isn't a better solution ayway.

Thanks Again!

Vic

 

[Dirk Goldgar]

The process seems a little slow but it WORKS!

It is slow, isn't it?

I'm not certain why the automation routine doesn't work but I'm not
so sure this isn't a better solution ayway.

The automation approach would doubtless be faster if you could get it to
work. I don't know why it isn't working, though.

Thanks Again!

You're welcome.