Error 3011 (1 Viewer)

[jfgambit]

Ok I'm stumped...I had the following code on the OnClick event of a command button:

Private Sub cmdExpCust_Click()
Dim strPath As String
Dim xlApp As Object
Dim xlSheet As Object
Dim rdCnt As Variant
Dim rwSet As Variant

txtDat = Format(Date, "mmddyy")
strPath = "C:\MSOFFICE\QBookExports\CUSTS\" & txtDat & "CUST.tab"
striif = "C:\MSOFFICE\QBookExports\CUSTS\" & txtDat & "CUST.IIF"
qryNM = "qryModQBExportCust"
DoCmd.TransferText acExportDelim, "CustExportSpec", qryNM, strPath

'Count the number of records in the Export
rdCnt = DCount("[InItm]", qryNM)
rwSet = (rdCnt + 3)
'open the Excel document move the information down two rows, add the headers and footers required by Quickbooks
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(strPath)

With xlApp
    .Application.Visible = True
    .Application.Workbooks(1).Sheets.select
    .Application.Rows(1).select
    .Application.Selection.Delete
    .Application.Rows("1:2").select
    .Application.Selection.Insert Shift:=-4121
    .Application.ActiveSheet.cells(1, 1).Value = "!CUST"
    .Application.ActiveSheet.cells(1, 2).Value = "NAME"
    .Application.ActiveSheet.cells(1, 3).Value = "BADDR1"
    .Application.ActiveSheet.cells(1, 4).Value = "BADDR2"
    .Application.ActiveSheet.cells(1, 5).Value = "BADDR3"
    .Application.ActiveSheet.cells(1, 6).Value = "BADDR4"
    .Application.ActiveSheet.cells(1, 7).Value = "BADDR5"
    .Application.ActiveSheet.cells(1, 8).Value = "CTYPE"
    .Application.ActiveSheet.cells(1, 9).Value = "TAXABLE"
    .Application.ActiveSheet.cells(1, 10).Value = "NOTEPAD"
    .Application.ActiveSheet.cells(1, 11).Value = "COMPANYNAME"
    .Application.ActiveSheet.cells(1, 12).Value = "CUSTFLD1"
    .Application.ActiveSheet.cells(1, 13).Value = "CUSTFLD2"
    .Application.ActiveSheet.cells(1, 14).Value = "CUSTFLD3"
    .Application.ActiveSheet.cells(1, 15).Value = "CUSTFLD4"
    .Application.ActiveSheet.cells(1, 16).Value = "JOBTYPE"
    .Application.ActiveSheet.cells(1, 17).Value = "JOBSTATUS"
    .Application.ActiveSheet.cells(2, 1).Value = "!ENDGRP"
    .Application.ActiveSheet.cells(rwSet, 1).Value = "ENDGRP"
    .Application.activeworkbook.Save
    .Application.activeworkbook.Close
    .Quit
End With
On Error Resume Next
Name strPath As striif
End Sub

It was running fine in one db, but when I transfered it over to a new one I receive the following error message:

Runtime Error '3011'
The Microsoft jet Database Engine could not find the the object '020204Cust.tab' Make sure the object exists and that you spelled its name and path name correctly.

The Debug is on the following line:

DoCmd.TransferText acExportDelim, "CustExportSpec", qryNM, strPath

Any ideas??

 

[dcx693]

Aren't you missing a \ character (or two) in here?:
strPath = "C:MSOFFICEQBookExportsCUSTS\" & txtDat & "CUST.tab"

Shouldn't it be?
strPath = "C:\MSOFFICE\QBookExports\CUSTS\" & txtDat & "CUST.tab"
or something like that?

 

[jfgambit]

No...code is correct (looks like when I copied it in PHP I lost the "\")

Code looks like this:

Private Sub cmdExpCust_Click()
Dim strPath As String
Dim xlApp As Object
Dim xlSheet As Object
Dim rdCnt As Variant
Dim rwSet As Variant

txtDat = Format(Date, "mmddyy")
strPath = "C:MSOFFICE\QBookExports\CUSTS\" & txtDat & "CUST.tab"
striif = "C:MSOFFICE\QBookExports\CUSTS" & txtDat & "CUST.IIF"
qryNM = "qryModQBExportCust"
DoCmd.TransferText acExportDelim, "CustExportSpec", qryNM, strPath

 

[ghudson]

Looks like you have not Dim'd all [txtDat, striif, qryNM] of your strings at the top. Does the code compile okay?

I suggest that you add a message box in between your code to see what "Access" is using for your strings.

txtDat = Format(Date, "mmddyy")
strPath = "C:MSOFFICE\QBookExports\CUSTS\" & txtDat & "CUST.tab"
striif = "C:MSOFFICE\QBookExports\CUSTS" & txtDat & "CUST.IIF"
qryNM = "qryModQBExportCust"

MsgBox txtDat
MsgBox strPath
MsgBox striif
MsgBox qryNM

DoCmd.TransferText acExportDelim, "CustExportSpec", qryNM, strPath

HTH

 

[jfgambit]

Ran the checks and everything is coming back fine...

MsgBox txtDat = 020204
MsgBox strPath = C:\MSOFFICE\QBookExports\CUSTS\020204CUST.tab
MsgBox striif = C:\MSOFFICE\QBookExports\CUSTS\020204CUST.iif
MsgBox qryNM = qryModQBExportCust

This form works in another Db. As soon as I import it into a new Db, it no longer works.

This is seriously driving me nuts!!!!

 

[ghudson]

Does the new db contain the export specification "CustExportSpec"?

Is the new db missing any references?

 

[jfgambit]

No....get this...

I added Docmd.OpenQuery qryNm
before Docmd.transfertext....

And it works...

What the heck??

 

[ghudson]

Out of curiosity, does this work?

DoCmd.TransferText acExportDelim, "CustExportSpec", "qryModQBExportCust", strPath

or this...

Dim qryNM as String
qryNM = "qryModQBExportCust"
DoCmd.TransferText acExportDelim, "CustExportSpec", qryNM, strPath

 

[jfgambit]

Found out it was the Cust ExportSpec...I added a field to the export and it was not mapped in the Export Specification on the new Db...

Figured it out by taking "CustExportSpec" out of the TransferText line.

Thanks for the help.