How to provide a view from sql server to an access table

Question

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.

Sincerely,

Sally

Answer 1

Hello Sally,

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).

---

Olaf Helper
Blog Xing

Answer 2

Hello Olaf,

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?

Thanks,

Sally

Answer 3

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.

Hello Sally,

The Wizard can't create new MS Access file, you have to (create a new empty and) select an ecxisting MDB file.

---

Olaf Helper
Blog Xing

Answer 4

Dear Olaf,

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.

Thanks,

Sally

Answer 5

Dear Sally,

I think the problem is with your current OLE DB provider. Download data connectivity components from this link

http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

choose Microsoft Office 12.0 Access Database Engine OLE DB Provider 

Thanks,

Anuraj

Answer 6

I created an empty file called sample.accdb, but I have a Microsoft Data Link Error:

Hello Sally,

How did you created the MDB file? Have you used MS Access for it and if with which version?

---

Olaf Helper
Blog Xing

Answer 7

Hello Olaf,

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.

Thank you.

Sally

Answer 8

Anuraj,

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.

Sincerely,

Sally