Run-Time Error when processing Export to .txt delimited file (1 Viewer)

abette

Member
Local time
Today, 08:41
Joined
Feb 27, 2021
Messages
85
Hi - I have a macro that is attached to a command button on my form that runs a step that executes (run code) a function called ExportData() a colleague created a long while ago. It exports the main database table to a txt delimited file that feeds a process to our SQL tables. Mind you this has been running for over a year without any issues.

I recently added data elements to the table and removed one data element and now when I click on the button to open the form I am receiving this message:

Microsoft Visual Basic dialog box displays with Run-time error '3438'
"The data being exported does not match the format described in the Schema.ini file.

My VBA module looks like this:

Public Function ExportData()
DoCmd.TransferText acExportDelim, "TblIOSDisputes Export Specification", "tblIOS_Disputes", "\\njfilecluster.cbhnp.org\public\Clinical Department\Clinical Review Database\IOSBackUp\IOSDisputeBackUp_" & Format(Date, "yyyy-mm-dd") & ".txt", True
End Function

When I skip this step in the macro the form displays correctly but this step is important to feeding a process for updating the SQL tables. The changes made to the table need to be defined to this schema.ini file but where do I find it? Is it a hidden file in the folder?

Thank you for your help.
 

June7

AWF VIP
Local time
Today, 04:41
Joined
Mar 9, 2014
Messages
4,459
Access most likely stores it in a deep hidden table. What a think you have to do is rebuild the specification. That means manually doing the export with the wizard and saving the specification. That's how it was created in the first place.
 

abette

Member
Local time
Today, 08:41
Joined
Feb 27, 2021
Messages
85
Access most likely stores it in a deep hidden table. What a think you have to do is rebuild the specification. That means manually doing the export with the wizard and saving the specification. That's how it was created in the first place.
Thank you for your reply. I have tried to recreate a new scheme file but when I do it expects me to run it through the Saved Exports window. I need it to run automatically through the VBA module above. It's still not working the way I want it too. So frustrating.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Feb 19, 2002
Messages
36,889
You need to find the Schemi.ini file and modify it to include the changes you made to the schema OR create a new schema.Ini file.

There are probably better ways to do this. Access can update SQL Server tables so you might want to redo this with a direct update rather than continue with this intermediate process.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:41
Joined
Oct 29, 2018
Messages
19,172
Hi - I have a macro that is attached to a command button on my form that runs a step that executes (run code) a function called ExportData() a colleague created a long while ago. It exports the main database table to a txt delimited file that feeds a process to our SQL tables. Mind you this has been running for over a year without any issues.

I recently added data elements to the table and removed one data element and now when I click on the button to open the form I am receiving this message:

Microsoft Visual Basic dialog box displays with Run-time error '3438'
"The data being exported does not match the format described in the Schema.ini file.

My VBA module looks like this:

Public Function ExportData()
DoCmd.TransferText acExportDelim, "TblIOSDisputes Export Specification", "tblIOS_Disputes", "\\njfilecluster.cbhnp.org\public\Clinical Department\Clinical Review Database\IOSBackUp\IOSDisputeBackUp_" & Format(Date, "yyyy-mm-dd") & ".txt", True
End Function

When I skip this step in the macro the form displays correctly but this step is important to feeding a process for updating the SQL tables. The changes made to the table need to be defined to this schema.ini file but where do I find it? Is it a hidden file in the folder?

Thank you for your help.
Hi. Which version of Access are you using?

The error message may be misleading. It mentions the Schema.ini file, that's why @Pat Hartman mentioned about checking and updating it. According to MS, you would usually find it at this location.
The schema information file is always named Schema.ini and always kept in the same directory as the text data source.
However, since your code is specifying a named spec, I think @Gasman is probably closer to analyzing your actual situation.
 

abette

Member
Local time
Today, 08:41
Joined
Feb 27, 2021
Messages
85
Thank you all for your assistance! I was able to update the export specification file. Thank you for the YouTube video it helped!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:41
Joined
Oct 29, 2018
Messages
19,172
Thank you all for your assistance! I was able to update the export specification file. Thank you for the YouTube video it helped!
Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom