When pulling data from a database using SQL how can I save my data to a file for importing to Access?

Answer 1

Why save data on other sources (like Excel) and then import it on Access, where you can do it directly? Remember, Excel rows are LIMITED and it MAY occur error (numerical data) when transferring data from Excel to Access (not counting yet SQL Server to Excel or other text file).

You can DIRECTLY pull data from SQL SERVER (or even MySQL) to your Access database by using ODBC.

On WinXP, go to Control Panel -> Administrative Tools -> Data Sources (ODBC).

On ODBC Data Source Administrator:
1- Click ADD button. Select SQL Server from the driver list.
2- Fill in name=test, description=test, Server=compServer (this is the name of the computer where the SQL server was).
3- Click also 'With SQL Server Authentication using a login and password entered by the user'. Fill-in Login ID=administrator, Password=adm. (login/password where from the SQL Server).
4- Then lastly, you may click the Test Data Source, and when you fill-in were all correct, you will be greeted by 'Test Completed Successfully!'.

Then go to Access:
1- Right click on and select 'LINK TABLES...', or you may use IMPORT. BTW, you can use: File ->Get External Data -> Link Tables...
2- Select Files of Type: ODBC Databases().
3- Select tab 'Machine Data Source', which is test.
4-Fill in the login and password.

BTW, in LINK TABLES, you are directly link the data from the SQL Server. You may safely use IMPORT first, where you will get just a copy of tables from SQL Server. Later, you may use LINK so you can directly add/edit/delete from SQL Server.

Very long? I can give you source for Visual Basic 6, where all you have to do is just supply five (5) variables!

Answer 2

If you are using SQL Enterprise Manager to get your data, right click on the table and follow the wizard for exporting data and select Excel for your output. Then you can import from excel.

If you are using query analyzer, have your results come back in a grid. Then you can highlight the entire grid and cut and past into a blank excel spreadsheet and use that for your import file to Access.

Answer 3

These techniques work with MS-SQL Server:

1) Anything described in the first two answers would work.

2) You could design and schedule a DTS package to automate the export process. A well designed DTS package in MS-SQL will give you total control over the SQL, conditional logic, has alerts enabled, output-format, etc. I strongly encourage this method for a recurring process where consistency is necessary.

3) You could use the bcp command prompt utility to generate an ASCII text file. Example:
bcp pubs..publishers out publishers.txt -c -T
Run from a command-prompt, this statement will output the pubs..publishers table to a .txt file. You could design a table-dump similar to this, and schedule it with the Task Scheduler, if you wanted to automate the process. This would be more easily accomplished with a DTS package, but there are conceivable applications where a command-line utility is preferable.

Answer 4

If you can't export the SQL results to an Excel spreadsheet like the above person mentioned, you should at least be able to export it to a comma seperated list, and then import that list into Access.

Answer 5

you could link to an exterior documents source interior of Excel. Its less than approaches. you'll desire an ODBC definition back to the sq. Server with the perfect credentials. From there, you could create a pivot table or truly do even with you want. pay attention of the 65k row reduce in older variations of Excel. Exporting to MS get admission to is likewise an concept. purely link the tables in get admission to back on your sq. Server db and carry out the perfect make table queries in get admission to. pay attention of the 2Gb. report length reduce for MS get admission to. good success.