cases but causes lots of issues down the road. When you use linked tables
Access runs all queries using the local Jet engine. So Jet will go to
MSSQL, bring in all the source data, and then run the query. It's actually
a little better than this in that it will only get the indexes and use those
to perform optimizations and request only required records, but it's still
much less efficient than other options.
Other options include using pass-through queries which basically means that
the queries tells Jet to ignore the SQL and just send it to MSSQL as is and
then return the response back to the front end. The advantage here is that
the query really runs on the server and you have full access to
server-specific functionality. Everything is still funneled through Jet but
Jet only has a minor role.
And then there's ADP's. Access Data Projects have only forms and reports in
the front end and always go directly to the MSSQL database for all database
access and processing. You can write queries and stored procedures in the
Access UI and they are real MSSQL views/sp's/triggers. The front end uses
ADODB/MSSQL OLEDB to directly access the back end and Jet is not involved at
all. The downside here is that there is no local storage option so for
something you want to cache locally, it still goes back to the server. Most
work-arounds involve using XML for local cache but the reality is you can
still write ADODB code to access a local Jet datasource.
HTH,
Sam
BTW, there are some great Access specific news groups where you could get
information from people that developing in Access full time--here mostly we
CFers just use it as our data store. Just get the normal usenet feed from
your news provider and do a search for groups that have access in the name.
I always liked comp.databases.ms-access.
-----------------------------------------------
Blog: http://www.rewindlife.com
Charts: http://www.blinex.com/products/charting
-----------------------------------------------
> -----Original Message-----
> From: Bruce, Rodney S HQISEC/Veridian IT Services
> [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, December 17, 2003 11:21 AM
> To: CF-Talk
> Subject: OT: Using Access front end for MS SQL
>
> Hi all
>
>
> Was wondering if anyone has tried this or has information.
>
>
> They want to use the already built Access front end
> (menus/forms/reports) application.
> But they want to use MS SQL.
>
>
> I know that tables can be linked from MS SQL to Access, which
> gets the information to the front end application.
> but I don't know of anyway to run SPs from MS SQL in Access.
>
>
> Also would Access's size limit still apply to the linked tables?
>
>
> Or is there another way to use the Access front end?
>
>
> Any information will be helpful.
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
