Wednesday, August 29, 2012 10:27 PM
I created a sql file and downloaded to CSV file, which poses some problem when a field contains a comma such as an address field.
To get around this problem, I would like to export this file/view to an Access Table which will hopefully preseve all of the original sql formatting.
Could you please show me how to export the file/view to an Access Table, or if you have better method that will eliminiate the comma delimited problem when a field contains a comma.
Thank you for your assistance.
Thursday, August 30, 2012 4:38 AMAnswerer
In common "text qualifier" are used your exporting text data which may contains column/row delimiter signs.
You could use the Import/Export Wizard of SSMS to export your data to a flat file or MS Access (or MS Excel).
Thursday, August 30, 2012 6:02 PM
I am having a problem to export the data to MS Access. I follow the instructions just like I did with the Excel.
This is my flow:
MS sql server management studio, right click and select tasks and select export data to --> welcome to sql server input and export wizard, click next, log onto user name and password. For destination, I select microsoft access and this is where I encounter the problem:
when I enter c:\excel\sample.mdb
the error says that I don't have such a file. Also it asks for user id and password that I don't know.
However, if I select destination as excel and enter the filename, there is no such problem.
Can you help me how I export the sql server file to an access table?
Friday, August 31, 2012 6:14 AMAnswerer
Friday, August 31, 2012 6:00 PM
I created an empty file called sample.accdb, but I have a Microsoft Data Link Error:
Test connection failed because of an error in initializing provider. Unrecognized database format e:\access\sample.accdb.
Please advise what is wrong with this.
Tuesday, September 04, 2012 8:22 AM
I think the problem is with your current OLE DB provider. Download data connectivity components from this link
- Marked As Answer by Ed Price - MSFTMicrosoft Employee, Owner Sunday, December 30, 2012 8:54 AM
Tuesday, September 04, 2012 10:24 AMAnswerer
Wednesday, September 05, 2012 4:09 PM
When I created an empty file *.mdb from MS Access, it works. I use MS Access 2007.
What is the difference between *.accdb and *.mdb?
Also the default when creating an MS Access file is *.accdb.
Can you explain why I have such an error.
Wednesday, September 05, 2012 4:15 PM
Thank you for your suggestion. I am using MS Access 2007. When I created the file in *.mdb it was ok.
The default for creating an MS Access file is *.accdb.
I am not familiar with the MS Office 12.0 Access Database Engine OLE DB Provider. If I download this version will it override my old version (I don't know which version). If I have problem after downloading, how do I recover my old file?
Thank you for your invaluable knowledge and advice.