Exporting From Access to a Dbase iV. via Object. Fails with error The Microsoft Jet Database engine could not find the object

chippy

Hi,
I've a VB script that creates a Access object in a word doc.
Here is the full script.

It works for all but the Export. Which always fails with a 3011 error.
If I do the same in Access as a straight Macro or script it works.
Add it as an object and it won't work.
HELP.
Sub Sub1()
Dim ACC As Variant
Dim AccFileName, TableName As String

Set ACC = CreateObject("Access.Application")
AccFileName = "G:\Current_copy\ccdata.mdb"
TableName = "OUTLETSERVIC"
ACC.OpenCurrentDatabase (AccFileName)

' Delete Records - works well
ACC.DoCmd.RunSQL "DELETE * FROM OUTLETSERVIC WHERE ID <>"""";", 0
ACC.DoCmd.Close acTable, TableName
' End delete

'Import Records - works well
ACC.DoCmd.TransferText acImportDelim, "OUTLETSERVICE_csv_gz Import
Specification", TableName, "G:\Working Files\OUTLETSERVICE.txt", True,
""
ACC.DoCmd.Close acTable, TableName
'End Import

'
################################################## ########################
' This is the bit the stuff up!!!!!!!!!!!!!!!!!!
'Export to DBF - fails with error The Microsoft Jet Database engine
could not find the object OUTLETSERVIC
ACC.DoCmd.TransferDatabase acExport, "dBase IV", "G:\Working
Files\", acTable, "OUTLETSERVIC", "G:\Working Files", False
ACC.DoCmd.Close acTable, TableName
'End Export
'
################################################## #########################

' Close the Database
ACC.CloseCurrentDatabase

MsgBox "Finished"
End Sub

Nov 13 '05 #1

Tom van Stiphout

On 27 Sep 2005 05:49:10 -0700, "chippy" <Ch************@gmail.com>
wrote:

Try it using foldernames and filenames that fit the 8.3 format
exclusively:
g:\work\test1.dbf

-Tom.

Hi,
I've a VB script that creates a Access object in a word doc.
Here is the full script.

It works for all but the Export. Which always fails with a 3011 error.
If I do the same in Access as a straight Macro or script it works.
Add it as an object and it won't work.
HELP.
Sub Sub1()
Dim ACC As Variant
Dim AccFileName, TableName As String

Set ACC = CreateObject("Access.Application")
AccFileName = "G:\Current_copy\ccdata.mdb"
TableName = "OUTLETSERVIC"
ACC.OpenCurrentDatabase (AccFileName)

' Delete Records - works well
ACC.DoCmd.RunSQL "DELETE * FROM OUTLETSERVIC WHERE ID <>"""";", 0
ACC.DoCmd.Close acTable, TableName
' End delete

'Import Records - works well
ACC.DoCmd.TransferText acImportDelim, "OUTLETSERVICE_csv_gz Import
Specification", TableName, "G:\Working Files\OUTLETSERVICE.txt", True,
""
ACC.DoCmd.Close acTable, TableName
'End Import

'
################################################# #########################
' This is the bit the stuff up!!!!!!!!!!!!!!!!!!
'Export to DBF - fails with error The Microsoft Jet Database engine
could not find the object OUTLETSERVIC
ACC.DoCmd.TransferDatabase acExport, "dBase IV", "G:\Working
Files\", acTable, "OUTLETSERVIC", "G:\Working Files", False
ACC.DoCmd.Close acTable, TableName
'End Export
'
################################################# ##########################

' Close the Database
ACC.CloseCurrentDatabase

MsgBox "Finished"
End Sub

Nov 13 '05 #2

chippy

Thanks, I'll give that a go. yet the code for import works well??

Nov 13 '05 #3

chippy

Renamed the code to the dos files names. ie "workin~1"
The problem continues.
I also tried to rename the access table to a short name (4 characters)
still no go.
Very annoying.
Tried it on Access 2000 and 2002

Nov 13 '05 #4

Chuck Grimsby

File names, Table names and field names are limited to 8 characters in
the DBF File format that Access uses. Nor can a file, table or Field
name have a space in it.
On 27 Sep 2005 05:49:10 -0700, "chippy" <Ch************@gmail.com>
wrote:

I've a VB script that creates a Access object in a word doc.
Here is the full script.
It works for all but the Export. Which always fails with a 3011 error.
If I do the same in Access as a straight Macro or script it works.
Add it as an object and it won't work.
HELP.
Sub Sub1()
Dim ACC As Variant
Dim AccFileName, TableName As String
Set ACC = CreateObject("Access.Application")
AccFileName = "G:\Current_copy\ccdata.mdb"
TableName = "OUTLETSERVIC"
ACC.OpenCurrentDatabase (AccFileName)
' Delete Records - works well
ACC.DoCmd.RunSQL "DELETE * FROM OUTLETSERVIC WHERE ID <>"""";", 0
ACC.DoCmd.Close acTable, TableName
' End delete
'Import Records - works well
ACC.DoCmd.TransferText acImportDelim, "OUTLETSERVICE_csv_gz Import
Specification", TableName, "G:\Working Files\OUTLETSERVICE.txt", True,
""
ACC.DoCmd.Close acTable, TableName
'End Import
'
################################################# #########################
' This is the bit the stuff up!!!!!!!!!!!!!!!!!!
'Export to DBF - fails with error The Microsoft Jet Database engine
could not find the object OUTLETSERVIC
ACC.DoCmd.TransferDatabase acExport, "dBase IV", "G:\Working
Files\", acTable, "OUTLETSERVIC", "G:\Working Files", False
ACC.DoCmd.Close acTable, TableName
'End Export
'
################################################# ##########################
' Close the Database
ACC.CloseCurrentDatabase
MsgBox "Finished"
End Sub

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Nov 13 '05 #5

David W. Fenton

"chippy" <Ch************@gmail.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

' This is the bit the stuff up!!!!!!!!!!!!!!!!!!
'Export to DBF - fails with error The Microsoft Jet Database
engine
could not find the object OUTLETSERVIC
ACC.DoCmd.TransferDatabase acExport, "dBase IV", "G:\Working
Files\", acTable, "OUTLETSERVIC", "G:\Working Files", False

Don't you need to provide the appropriate filename with extension
for the next-to-the-last argument? I just tested it, and the path
should be the 3rd argument, and the filename the one where you just
repeat the path again.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #6

chippy

Whats annoying is that the code works fine as is if a sub inside access
itself.
If you leave the 3rd argumant as a path it creates a default named
dbase.
Anyway
I tried, renaming all tables, and folders etc to < 8 characters but it
still fails the same way.:
*
ACC.DoCmd.TransferDatabase acExport, "dBase IV", "C:\data\work\",
acTable, "mytable", "C:\data\Work\cdata.dbf", False

I also tried exporting a paradox still same error.
*
I was successful in trying to export as a Access database but this is
not what I need.
Very anoying

Nov 13 '05 #7

David W. Fenton

"chippy" <Ch************@gmail.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

Whats annoying is that the code works fine as is if a sub inside
access itself.
If you leave the 3rd argumant as a path it creates a default named
dbase.
Anyway
I tried, renaming all tables, and folders etc to < 8 characters
but it still fails the same way.:
*
ACC.DoCmd.TransferDatabase acExport, "dBase IV", "C:\data\work\",
acTable, "mytable", "C:\data\Work\cdata.dbf", False
"C:\data\Work\cdata.dbf" should be "cdata.dbf". You provide the path
in the 3rd argument and the filename in the 6th.
I also tried exporting a paradox still same error.
*
I was successful in trying to export as a Access database but this
is not what I need.
Very anoying

This should work:

ACC.DoCmd.TransferDatabase acExport, "dBase IV", "C:\data\work\",
acTable, "mytable", "cdata.dbf", False

If it doesn't, then I don't know what the issue is.

The reason things are different for Access MDBs and for DBF files is
that Access puts multiple tables in a single MDB file, whereas in
DBF, it's one table per file.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 13 '05 #8

chippy

Thanks davis for your help. Still can't make it work as an object in
word so have made it launch access and export. This works and the code
is identical without the Object call, I just copied my code in.
Must be a bug with access objects.

Nov 13 '05 #9