nulad
asked on
Function - DoCmd.TransferText
There is a Function that seems to have stopped working properly (it was created in Access 2003, we now have Access 2007).
The below function uses a SELECT QUERY "Query to export data to Text File". After running the below Function, the following is displayed "Microsoft Office Access was unable to append all the data to the table.........
FUNCTION CODE:
'Opens an MS Excel dialog box to gather path and file name.
'Path and file name are used in the DoCmd.TransferText function.
'The query "Query to export data to Text File", results are saved as
'a text delimited file to the path and file name
'collected from the user in the MS Excel dialog box.
Public Function Browse4Data ()
On Error GoTo Err_Browse4Data
Dim objsrc As Object
Dim strpath As String
Set objsrc = GetObject("", "Excel.application")
strpath = objsrc.Application.GetSave
DoCmd.TransferText acExportDelim, , "Query to export data to Text File", strpath, No
Set objsrc = Nothing
Err_Browse4Data:
Exit Function
End Function
Why is the "Microsoft Office Access was unable to append all the data to the table" dialog box appearing? It is using a SELECT query. The query works fine on its own.
The below function uses a SELECT QUERY "Query to export data to Text File". After running the below Function, the following is displayed "Microsoft Office Access was unable to append all the data to the table.........
FUNCTION CODE:
'Opens an MS Excel dialog box to gather path and file name.
'Path and file name are used in the DoCmd.TransferText function.
'The query "Query to export data to Text File", results are saved as
'a text delimited file to the path and file name
'collected from the user in the MS Excel dialog box.
Public Function Browse4Data ()
On Error GoTo Err_Browse4Data
Dim objsrc As Object
Dim strpath As String
Set objsrc = GetObject("", "Excel.application")
strpath = objsrc.Application.GetSave
DoCmd.TransferText acExportDelim, , "Query to export data to Text File", strpath, No
Set objsrc = Nothing
Err_Browse4Data:
Exit Function
End Function
Why is the "Microsoft Office Access was unable to append all the data to the table" dialog box appearing? It is using a SELECT query. The query works fine on its own.
If you want to dump the results of a query to a file you can use outputto
eg
DoCmd.OutputTo acOutputQuery, "QueryName", acFormattxt,"myfilename"
eg
DoCmd.OutputTo acOutputQuery, "QueryName", acFormattxt,"myfilename"
Your code works fine form me, but you must change the last TransferText argument from:
No
to...
"False"
No
to...
"False"
Correction:
Your code works fine form me, but you must change the last TransferText argument from:
No
to...
False
Your code works fine form me, but you must change the last TransferText argument from:
No
to...
False
ASKER
Thank you for your replies.
1) The "Trust Settings" are set up for the folder in which the Access db resides.
2) I changed the last TransferText argument from: No to False
....Same results. Still recieve the "Microsoft Office Access was unable to append all the data to the table".
3) Tried: DoCmd.OutputTo acOutputQuery, "QueryName", acFormattxt,"myfilename"
This did create a text file, but it was not a text delimited file without header information.
4) When I run the query by itself, it comes up with data. If I then go to "External Data > Export > Text File" and do NOT select the "Export data with formatting and layout", then select "OK" , I receive the message "Too few parameters. Expected 4". If I DO select the "Export data with formatting and layout" it does work, but it is not a delimited text file.
5) Relating to above #4, the the message "Too few parameters. Expected 4". There is a field in the query with 4 parameters:
getPassword([GOVERNMENT_ID
__________________________
Public Function getPassword(sGovID As Variant, sFirst As Variant, sLast As Variant, sBirth As Date) As Variant
Dim sFirstFour As String
Dim sMiddleTwo As String
Dim sLastTwo As String
sFirstFour = ""
sMiddleTwo = "__"
sLastTwo = UCase(Left(sFirst, 1)) & LCase(Left(sLast, 1))
If sGovID <> "000000000" Then
sFirstFour = Right(sGovID, 4)
Else
If sBirth > #1/1/1900# Then
sFirstFour = Left(sBirth, 4)
Else
sFirstFour = "0000"
End If
End If
getPassword = sFirstFour & sMiddleTwo & sLastTwo
End Function
Any suggestions?
1) The "Trust Settings" are set up for the folder in which the Access db resides.
2) I changed the last TransferText argument from: No to False
....Same results. Still recieve the "Microsoft Office Access was unable to append all the data to the table".
3) Tried: DoCmd.OutputTo acOutputQuery, "QueryName", acFormattxt,"myfilename"
This did create a text file, but it was not a text delimited file without header information.
4) When I run the query by itself, it comes up with data. If I then go to "External Data > Export > Text File" and do NOT select the "Export data with formatting and layout", then select "OK" , I receive the message "Too few parameters. Expected 4". If I DO select the "Export data with formatting and layout" it does work, but it is not a delimited text file.
5) Relating to above #4, the the message "Too few parameters. Expected 4". There is a field in the query with 4 parameters:
getPassword([GOVERNMENT_ID
__________________________
Public Function getPassword(sGovID As Variant, sFirst As Variant, sLast As Variant, sBirth As Date) As Variant
Dim sFirstFour As String
Dim sMiddleTwo As String
Dim sLastTwo As String
sFirstFour = ""
sMiddleTwo = "__"
sLastTwo = UCase(Left(sFirst, 1)) & LCase(Left(sLast, 1))
If sGovID <> "000000000" Then
sFirstFour = Right(sGovID, 4)
Else
If sBirth > #1/1/1900# Then
sFirstFour = Left(sBirth, 4)
Else
sFirstFour = "0000"
End If
End If
getPassword = sFirstFour & sMiddleTwo & sLastTwo
End Function
Any suggestions?
I'm confused, so lets keep this simple.
First lets see if the Access Export will work on it's own.
*Before* we worry about the Excel stuff.
To Export a Text file, Delimited and without Headings, you need to create an Export specification:
DoCmd.TransferText acExportDelim, "ExportSpecificationName",
So you must do the export once *Manually* to create the Export Spec
(Before clicking finish in the Export wizard, click "Advanced" to save the Export settings.)
*Then* run the code above.
Does this create the correct file, in the correct format?
Yes or No?
JeffCoachman
First lets see if the Access Export will work on it's own.
*Before* we worry about the Excel stuff.
To Export a Text file, Delimited and without Headings, you need to create an Export specification:
DoCmd.TransferText acExportDelim, "ExportSpecificationName",
So you must do the export once *Manually* to create the Export Spec
(Before clicking finish in the Export wizard, click "Advanced" to save the Export settings.)
*Then* run the code above.
Does this create the correct file, in the correct format?
Yes or No?
JeffCoachman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...and inserting your own Table/query name and Full FileName
Please run a version of the query without any parameters, ...again, let's see if the export works, then move on from there...
ASKER
boag2000,
Thank you for your comments. I have taken your advice and tried to recreate the query, field by field. When exporting the data manually, without parameters, everything is o.k. until I add the field "getPassword([GOVERNMENT_I
When trying to export as text without formatting and layout, It generates the error "Data type mismatch in criteria expression". The getPassword function this field uses is listed above.
Thank you for your comments. I have taken your advice and tried to recreate the query, field by field. When exporting the data manually, without parameters, everything is o.k. until I add the field "getPassword([GOVERNMENT_I
When trying to export as text without formatting and layout, It generates the error "Data type mismatch in criteria expression". The getPassword function this field uses is listed above.
This is why I recommended running he query *Without* any other things (Function Criteria).
We are trying to first see if the query will work on its own...
We are trying to first see if the query will work on its own...
ASKER
It works on it's own without the other things (Function Criteria). Everthing is o.k. until I add the field with the function criteria. After that, it will NOT export as text without formatting and layout. It WILL export using the other methods.
Then it seems like you need to take a close look at your function...
*On it's own* does the function return the correct result?
Does it handle nulls?, invalid entries?
Is the formatting really needed?
There is no real "Formatting" (except for fonts)in a query, per se...
Can you explain a bit about this requirement?
The goal of Exporting is to get the "Data", ...you can always format the data anyway you like in the target application...
Keep us posted.
;-)
JeffCoachman
*On it's own* does the function return the correct result?
Does it handle nulls?, invalid entries?
Is the formatting really needed?
There is no real "Formatting" (except for fonts)in a query, per se...
Can you explain a bit about this requirement?
The goal of Exporting is to get the "Data", ...you can always format the data anyway you like in the target application...
Keep us posted.
;-)
JeffCoachman
ASKER
The query worked successfully. The only issue was when trying to export it to a text file without formatting (which was needed). It successfully exported as every other file type except a text file without formatting. Must be a "bug" after one of the Windows/Office updates, because it always worked in the past.
I ended up using a Make Table query which included all of the functions and parameters. The newly created table did export successfully as a text file without formatting. This solved the issue.
Thank you for all your time and help. It helped me think in a different direction to solve the issue. Thank you again.
I ended up using a Make Table query which included all of the functions and parameters. The newly created table did export successfully as a text file without formatting. This solved the issue.
Thank you for all your time and help. It helped me think in a different direction to solve the issue. Thank you again.
ASKER
The query worked successfully. The only issue was when trying to export it to a text file without formatting (which was needed). It successfully exported as every other file type except a text file without formatting. The issue must have occured after one of the Windows/Office updates, because it always worked successfully in the past.
I ended up using a Make Table query which included all of the functions and parameters. The newly created table did export successfully as a text file without formatting. This solved the issue.
I ended up using a Make Table query which included all of the functions and parameters. The newly created table did export successfully as a text file without formatting. This solved the issue.
MS Access Button > Trust Center > Trust Center Settings