P: n/a
|
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 | |
Share this Question
P: n/a
|
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 | |
P: n/a
|
Thanks, I'll give that a go. yet the code for import works well?? | |
P: n/a
|
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 | |
P: n/a
|
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 | |
P: n/a
|
"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 | |
P: n/a
|
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 | |
P: n/a
|
"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 | |
P: n/a
|
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. | | This discussion thread is closed Replies have been disabled for this discussion. | | Question stats - viewed: 6264
- replies: 8
- date asked: Nov 13 '05
|