By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Text Export Problem Run-Time Error '13'

P: n/a
I have an application that will export two files to fixed width text
to combine as a single text file. The first export will be a query
containing header information for the file, the second query will be a
table containing transactions. I have a routine that uses a command
button to set up the export and a module that takes care of the merge.
I've scraped this together using posts on this group The command
button portion works but I get a Run-Time Error '13': Type mismatch
when it gets to the module at this line ..

Set rstOutputData = DB.OpenRecordset(strQuery, dbOpenDynaset)

I have been round and round with this and can not figure out where
I'm going wrong. I would sure appreciate any guidance here.

Thanks in advance,

Michael

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++++
Command button

Private Sub Command0_Click()

Dim strFirstQuery As String
Dim strSecondQuery As String
Dim strPath As String
Dim strOutputData As String
Dim strOutputQuery As String
Dim intFileNumber As Integer
strPath = "c:\Header.txt"
strFirstQuery = "QryHeader"

'Output Batch Header
DoCmd.TransferText acExportFixed, "EE2", strFirstQuery, strPath
strSecondQuery = "QryEmployees"

'Procedure ammends text file created above
Call StringBuild(strPath, strSecondQuery)

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++++++++
String Build Portion

Public Function StringBuild(strPath As String, strQuery As String) As
String
Dim DB As Database
Set DB = CurrentDb

Dim intFileNumber As Integer
Dim strBuildString As String
Dim qf As QueryDef
Dim rstOutputData As Recordset
Dim intI As Integer

Set rstOutputData = DB.OpenRecordset(strQuery, dbOpenDynaset)' <<<

With rstOutputData
If .EOF = False And .BOF = False Then
Do Until .EOF
For intI = 0 To .Fields.Count - 1
strBuildString = strBuildString & .Fields(intI).Value
If intI < .Fields.Count - 1 Then
strBuildString = strBuildString & " , "
Else
strBuildString = strBuildString & vbCrLf
End If
Next

intFileNumber = FreeFile
Open strPath For Append As intFileNumber
Print #intFileNumber, strBuildString;
Close intFileNumber

strBuildString = ""
.MoveNext
Loop
End If
End With

End Function
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You don't say what version of Access you're using, but I'll guess it's
either 2000 or 2002.

The fact that Set DB = CurrentDb doesn't cause an error shows that you've
set a reference to DAO, but I suspect you did not remove the reference to
ADO when you did so.

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rstOutputData as DAO.Recordset (to guarantee an ADO recordset, you'd
use Dim rstOutputData As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Michael" <Mi******@HotMail.Com> wrote in message
news:43**************************@posting.google.c om...
I have an application that will export two files to fixed width text
to combine as a single text file. The first export will be a query
containing header information for the file, the second query will be a
table containing transactions. I have a routine that uses a command
button to set up the export and a module that takes care of the merge.
I've scraped this together using posts on this group The command
button portion works but I get a Run-Time Error '13': Type mismatch
when it gets to the module at this line ..

Set rstOutputData = DB.OpenRecordset(strQuery, dbOpenDynaset)

I have been round and round with this and can not figure out where
I'm going wrong. I would sure appreciate any guidance here.

Thanks in advance,

Michael

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++
+++++++ Command button

Private Sub Command0_Click()

Dim strFirstQuery As String
Dim strSecondQuery As String
Dim strPath As String
Dim strOutputData As String
Dim strOutputQuery As String
Dim intFileNumber As Integer
strPath = "c:\Header.txt"
strFirstQuery = "QryHeader"

'Output Batch Header
DoCmd.TransferText acExportFixed, "EE2", strFirstQuery, strPath
strSecondQuery = "QryEmployees"

'Procedure ammends text file created above
Call StringBuild(strPath, strSecondQuery)

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++
+++++ String Build Portion

Public Function StringBuild(strPath As String, strQuery As String) As
String
Dim DB As Database
Set DB = CurrentDb

Dim intFileNumber As Integer
Dim strBuildString As String
Dim qf As QueryDef
Dim rstOutputData As Recordset
Dim intI As Integer

Set rstOutputData = DB.OpenRecordset(strQuery, dbOpenDynaset)' <<<

With rstOutputData
If .EOF = False And .BOF = False Then
Do Until .EOF
For intI = 0 To .Fields.Count - 1
strBuildString = strBuildString & .Fields(intI).Value
If intI < .Fields.Count - 1 Then
strBuildString = strBuildString & " , "
Else
strBuildString = strBuildString & vbCrLf
End If
Next

intFileNumber = FreeFile
Open strPath For Append As intFileNumber
Print #intFileNumber, strBuildString;
Close intFileNumber

strBuildString = ""
.MoveNext
Loop
End If
End With

End Function

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.