If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Design considerations for an Access project and SQL Backend.
Wondering if anyone had thoughts on how to link to SQL Server. I have read
about linked tables and pass through queries, but I would prefer to use ADO 2.0 and sprocs on the server to do my selects and processing. I succuessfully wrtote VBA code to assign a recordset to a report and it seems to work well. Any links or books releavant to this design would be helpful. Mike |
#2
|
|||
|
|||
Design considerations for an Access project and SQL Backend.
The newsgroup for ADP is m.p.access.adp.sqlserver.
The futur of ADP looks dim at that moment as this technology is probably in the process of beeing replaced with .NET for advanced stuff or MDB with linked tables and passthrough queries for entry level interfaces. They are also other solutions like using unbound forms and make the queries/updates all by yourself. Also, even if the futur of ADP looks dim, nothing forbid you to use them in the meantime; alone or in association with other types of frontends. Why using VBA code to assign a recordset to a report in ADP? -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Computermike" wrote in message ... Wondering if anyone had thoughts on how to link to SQL Server. I have read about linked tables and pass through queries, but I would prefer to use ADO 2.0 and sprocs on the server to do my selects and processing. I succuessfully wrtote VBA code to assign a recordset to a report and it seems to work well. Any links or books releavant to this design would be helpful. Mike |
#3
|
|||
|
|||
Design considerations for an Access project and SQL Backend.
I'm with Sylvain here. There is little point in using an Access front end
if you are not going to use bound forms. There is just such an advantage to it that any potential minor speed improvement by using disconnected recordsets is simply not worth the coding effort. If you really like to code that much, go with VB.net or C. I have been using Access as a front end for major RDBMS' since version 2.0 in the early 90's. Access works fine against linked ODBC tables, especially if you design your application with that purpose in mind from the beginning. There were only a couple of times during that period where I needed to resort to pass-through queries or use views/stored procedures. In the case of the pass-through, I was replacing an entire table with a refreshed version (don't ask ) and the delete from Access took way too long due to the fact that Access gives you the option to cancel the delete so which causes it to have to cache the to-be-deleted records. The other cases were complex reports that were working with tables containing millions of rows. I created server views to get the data in some cases and in others, I created SQL on the fly and ran it as a pass-through query. Access can actually handle millions of rows. The problem was that too much of the processing had to be done locally due to the calculations and functions that needed to be used and the issue was network bandwidth. It takes a lot of time to move 9 million rows around a network. "Computermike" wrote in message ... Wondering if anyone had thoughts on how to link to SQL Server. I have read about linked tables and pass through queries, but I would prefer to use ADO 2.0 and sprocs on the server to do my selects and processing. I succuessfully wrtote VBA code to assign a recordset to a report and it seems to work well. Any links or books releavant to this design would be helpful. Mike |
#4
|
|||
|
|||
Design considerations for an Access project and SQL Backend.
I'm using VBA because that's the development language for Access.
What other methods are available to associated a strored procedure to a report. I used ADO in the same manner I would if I was building ASP.NET or a Windows app. It's simple code in the report open event. Works fast. "Sylvain Lafontaine" wrote: The newsgroup for ADP is m.p.access.adp.sqlserver. The futur of ADP looks dim at that moment as this technology is probably in the process of beeing replaced with .NET for advanced stuff or MDB with linked tables and passthrough queries for entry level interfaces. They are also other solutions like using unbound forms and make the queries/updates all by yourself. Also, even if the futur of ADP looks dim, nothing forbid you to use them in the meantime; alone or in association with other types of frontends. Why using VBA code to assign a recordset to a report in ADP? -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Computermike" wrote in message ... Wondering if anyone had thoughts on how to link to SQL Server. I have read about linked tables and pass through queries, but I would prefer to use ADO 2.0 and sprocs on the server to do my selects and processing. I succuessfully wrtote VBA code to assign a recordset to a report and it seems to work well. Any links or books releavant to this design would be helpful. Mike |
#5
|
|||
|
|||
Design considerations for an Access project and SQL Backend.
The usual method with ADP would be to set the Record Source to the name of
the SP, set the Record Source Qualifier to dbo and use the Input Parameters to pass parameters to the SP. You method works fine with Reports; excerpt for the cases where you will have sub-reports: assigning recordsets to sub-reports will be a real pain in the a**. -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Computermike" wrote in message ... I'm using VBA because that's the development language for Access. What other methods are available to associated a strored procedure to a report. I used ADO in the same manner I would if I was building ASP.NET or a Windows app. It's simple code in the report open event. Works fast. "Sylvain Lafontaine" wrote: The newsgroup for ADP is m.p.access.adp.sqlserver. The futur of ADP looks dim at that moment as this technology is probably in the process of beeing replaced with .NET for advanced stuff or MDB with linked tables and passthrough queries for entry level interfaces. They are also other solutions like using unbound forms and make the queries/updates all by yourself. Also, even if the futur of ADP looks dim, nothing forbid you to use them in the meantime; alone or in association with other types of frontends. Why using VBA code to assign a recordset to a report in ADP? -- Sylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) "Computermike" wrote in message ... Wondering if anyone had thoughts on how to link to SQL Server. I have read about linked tables and pass through queries, but I would prefer to use ADO 2.0 and sprocs on the server to do my selects and processing. I succuessfully wrtote VBA code to assign a recordset to a report and it seems to work well. Any links or books releavant to this design would be helpful. Mike |
Thread Tools | |
Display Modes | |
|
|