Question Point existing Access db to SQL or migrate queries?

[DiverGuy]

Good afternoon. Your thoughts and input would be appreciated on this one.

We have an existing Access database, complete with reports that pull data from fairly complex queries. The tables were migrated to a SQL server and the data input/manipulation forms have been recreated as asp.net forms. The managers that use the reports (a fairly small group so concurrent access isn't a problem) are very satisfied with the layout, format and content of the reports so we would like to continue using those.

I created an .adp project and imported the forms and reports but importing the queries, which the reports depend upon, was not an available option. Is there a way to take the existing Access database and point it at the SQL server as the source of data so the queries will work and the reports will populate? Or is the only option to rebuild the 103 queries in the .adp project?

Thanks for any ideas you might have.

John

 

[oumahexi]

You can link your Access database to SQL Server though the DSN.

While it can be done and you can use the two together you really need to think about the future. Databases have a nasty habit of growing out of propoportion and if yours does can your Access side hold up or will you be thinking of moving the whole thing over to SQL in the end anyway? It may be that you'll benefit from restructuring it into SQL now before it becomes a nightmare.

I don't mean to scare monger, I've been living the nightmare for about a year now

 

[DiverGuy]

You can link your Access database to SQL Server though the DSN.

Thanks. That actually lead me down the path I needed. Googling on those phrases revealed these links, which detail how to set up the ODBC connection to the SQL server (http://www.mssqltips.com/tip.asp?tip=1470) and then connect Access to SQL through the newly created DSN (http://www.mssqltips.com/tip.asp?tip=1480). The rest was easy.

Appreciate the help.

John