Access and SQL 1

[rkdcomp]

Hope this can be simple. My company has a SQL database that is beining updated by another program. What we want to do is create an Access "front-end" to link to the SQL database so we can creat custom reports that can be accessed via some forms.

I know this may be doable, but just how would we go about it.

We would NOT be adding any data to the database, just using the data already there.

Thanks,

Richard in Tulsa

 

[sawdustex]

While in Access and viewing the table objects, right click and select 'link tables'. On the 'files of type' select 'ODBC Daabases. You will then be prompted to Select a data source. Here you may create a 'DSN' pointing to your SQL database. After you create the 'DSN', a list of tables will appear for you to select from. After you select the tables they will appear in your Access Data Objects.

 

[tlbroadbent]

If you want good performance, avoid linked tables. Create an Access Project. MS Access Help contains info about Access Projects. The following quote was taken directly from Access Help.

"A Microsoft Access project (.adp) is a new type of Access file that provides efficient, native-mode access to a Microsoft SQL Server database through the OLE DB component architecture."

If you don't have Access 2000 or don't want to use ADP, you should consider using Pass-through queries which are read-only and perform better than linked tables in most cases. Pass-Through queries are also documented in Access Help. You can find a lot of info on the MS website. The following link is an excellent place to start when integrating Access and SQL Server.

http://msdn.microsoft.com/library/en-us/dnacc97/html/acsqlres.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.