Access with SQL -- sql server misc

sql server misc : Access with SQL

oj
1/21/2004 3:00:18 PM

The big question would be if you can get to the Access database at all over
the WAN, permission/security wise. For instance, you have a sqlserver
(server1) running under 'Joe' account and this account in no way can
access/connect to a remote computer (server2) due to windows permission, you
will not be able to get to the Access database.

If you can resolve the windows security issue, you can get to the Access
database either through a linked server (sp_addlinkedserver), ad-hoc
distributed query (opendatasource/openrowset). You can look these up in sql
book online for more info.

--
-oj
http://www.rac4sql.net

[quoted text, click to view]

Ernie Adsett
1/21/2004 7:55:27 PM

We would like to have SQL connect with an Access database over a WAN.
Is the best way to use MSDE and allow SQL to connect directly to the
Access database or to convert this database every so often to SQL?

Thanks!

Ernie Adsett

James Goodman
2/9/2004 4:44:42 PM

Is Access forming a front-end db for a SQL DB?
Would it not be possible to utilise some kind of web site (ASP) or similar.
It will run much faster over a WAN.

Or are you 'replicating' the SQL Server db to an AccessDB?
You dont need MSDE to connect to a SQL Server DB. MSDE is the 'desktop'
version of SQL Server.

--
Cheers,

James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures

Kevin3NF
3/9/2004 1:23:17 PM

I'm in the final stages of converting an MDB to ADP. It is much faster, but
there are gotchas to work around. If you don't already know SQL Server, the
learning curve is even steeper.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

[quoted text, click to view]

User
3/9/2004 2:34:15 PM

You're too late. The latest fad is already ADO.NET which bears little
resembalance to ADO.
No need to wonder - you are guaranteed to be victimized by a new fads on a
regular basis.

[quoted text, click to view]

dp
3/9/2004 7:03:17 PM

I would have thought an Access ADP would be the trick for what this many has
asked for.

The fact that nobody in this thread has even mentioned these ADPs is
somewhat troubling to me. Is this dead end stuff? When I first read about
ADP's, I thought, "Hey! I can leverage my existing knowledge of how to get
Access to get up and dance, and take advantage of the speed of SQL at the
same time" In the months (well year now) that I've been wanting to move to
this approach, I have heard less and less about ADPs.

Anyone have any input on ADPs, and why they might not be a good solution for
a client-serverh app like this?

-BrianDP

[quoted text, click to view]

dp
3/9/2004 9:30:32 PM

Well, I'm learning the curve. Data types are different, there are views,
and these crazy SP_MOFO_THIS. No, I'm excited about the power that SQL will
provide. I had one task - it was one screen that queried two tables HUGE
tables, > 1M recs each, and filtered out the information, sliced it and
diced it a certain way, then reported on it. The version I wrote as an MDB,
when you hit the "go" button, would take about 30 seconds. In the new ADP I
wrote, it takes 3 seconds.

This increase in speed is enough for me. I'll learn ADO, I'll learn to
store my procedures. I'll learn about views, and such.

What my real question though is, "Is microsoft planning on dropping this
'avenue' any time in the near future?" I don't mind learning a new
language, or new methods for an older language, what I DO mind, is learning
some new microsoft strategy that is only going to be "hot stuff" for about 5
months before they change the synatax, and start calling it something else -
like with DAO to ADO. Well, for whatever reason they had to go that
direction fine, I just want to know that if I sink my teeth into ADO, that
I'll be able to be comfortable there for a while (say 5 years or so) before
microsoft some along with the .NET and scoops me up into some new
handy-dandy record-handling language.

-Brian

[quoted text, click to view]

dp
3/12/2004 1:56:57 PM

Will ADO as I know it, the connection string, etc, will that continue to
morph over time as well? Every time Microsoft releases a new version of
Access/SQL Server I'm going to have to go tweek my code? This doesn't seem
right. I mean, I realize people aren't (for the most part) out there still
programming in Cobol, and wondering why people are expecting them to move
on. I just want to know that Microsoft won't drop support for ADO in the
near future.

-Brian

User
3/13/2004 8:56:55 AM

I expect it will continue to be supported for some time.
However it will continue to "morph". Morphing is not the problem.
There is a special place in DLL hell for users of Access - Jet - MDAC.

You stand a good chance of
1) do a forced upgrade
2) having such upgrade break your application
3) having data reliablity problems due to 1 and 2.

Unlike the SQL people, the Access - Jet - MDAC
people don't seem to understand that data is important.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server misc

View Other Months
January 2003
March 2003
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
July 2007
August 2007
September 2007
October 2007

home · blog · groups

Questions? Comments? Contact the dn