Error while Exporting into access db - Resource Exceeded

[kasisbook]

So, I've got 3 fairly large datasets (~ 1.5mil records each. 670mb total) that I need to be exported from SAS to Access as tables in a new database. We have code that is part of a weekly automated process, and like every other week the code bombs out with this error: ERROR: CLI execute error: [Microsoft][ODBC Microsoft Access Driver] System resource exceeded. Unfortunately, I have to use Access instead of a real database (it needs to be accessible to many other people who don't have/don't know other software), so I can't just upgrade to MySQL or anything. Any suggestions on how to work with/around this error? I'd be hugely thankful as this has been a pretty consistent thorn in my side!

[ballardw]

It might help to provide the code you are using to connect SAS to Access.

Are your writing into an existing table or is SAS creating one from scratch? How many tables are already in the database when the SAS bit starts? At 670MB, assuming no expansion of fields on transfer to Access, you are already at nearly one-third of the allowed size of the database.

You don't mention the number of variables involved. If you are pushing the 255 variable limit occasionally that would be a concern as well.

[Reeza]

Write to a text file and link the Access DB to the text file as the main source.

https://support.microsoft.com/en-us/office/import-or-link-to-data-in-a-text-file-d6973101-9547-4315-....

[RacheLGomez123]

There are several possible causes for errors when exporting data into an Access database. Here are some tips to help you fix the problem:

Check the Access database: Make sure the database is not corrupted and that it has the correct structure and format to receive the data you are exporting.

Check the data being exported: Make sure that the data being exported is in the correct format and that it meets any validation rules or data type restrictions that may be in place.

Check the export process: Make sure that you are using the correct export method and that you have the necessary permissions and privileges to export data.

Check for duplicates: Check for any duplicate data in the Access database that may be causing the error.

Check for missing or incorrect references: Make sure that any references or dependencies required by the Access database are correctly set up.

Check the file paths: Make sure that the file paths are correct and that the files are accessible by the export process.

Check for conflicts with other applications: Make sure that there are no conflicts with other applications or processes running on your computer that may be interfering with the export process.

Try restarting the computer: Sometimes, simply restarting the computer can help resolve the error.

Regards,
Rachel Gomez