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

[abette]

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]

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]

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.

 

[Gasman]

No, you need to save the export specification.

However I seem to recall it is a little misleading in the way it has to be saved.?

https://www.youtube.com/results?search_query=save+access+export+specification

I seem to recall you click on the Advanced button, set your options and THERE is where you save the spec to use in your export.?

 

[Pat Hartman]

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]

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]

Thank you all for your assistance! I was able to update the export specification file. Thank you for the YouTube video it helped!

 

[theDBguy]

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.