By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,192 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,192 IT Pros & Developers. It's quick & easy.

OLE to SQL Server

P: n/a
I've been told by my IT people to set up a connection to the SQL
Server using OLE instead of ODBC. Apparently it is faster. I have no
idea how to even begin. Does anyone have an example of setting up
this connection on the fly? I will be distributing the database to
about 20 people and really don't want to do it manually on each PC. I
have the syntax to use for a trusted connection
""Provider=SQLOLEDB;Data Source=Your_Server_Name;Initial
Catalog=Your_Database_Name;Integrated Security=SSPI;". How do I see
the tables to build queries and reports?

Help, I'm in a whole new territory.

Thanks,
Laura
Nov 3 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Hi Laura,

Here is a code sample for using ADODB (what would be OLEDB in Access to
sql server) - make sure you have a reference in Tools\References to
Microsoft ActiveX Data Objects 2.x Library (2.5 or higher)

----------------------------------
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=YourServer;Database=YourDB;Trusted_Connecti on=Yes"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select * from someTbl"

Set RS = cmd.Execute

Do While Not RS.EOF
Debug.print RS(0)
RS.MoveNext
Loop

----------------------------------

ADODB offers a broader range of operations that can be performed (with
less restrictions like character size limits of 245 per field - not 255
- and less contention issues like record locking issues) than ODBC
between Access and sql server.

As for distribution - since an MDB is just a file - you can email the
updated file to each of your users. The .Net environment has addressed
the issue of distribution (along with a host of other issues) and has a
feater called "Click Once" for distributing applications accross an
intranet/internet. You can distribute pretty much anything supported by
windows (.exe, .mdb, .xls, .dll, .txt, jpg, ...). I mention this
because distribution has always been an issue/hassel with mdb's. The
downside of emailing mdb files is that users may end up with multiple
copies of the mdb and invoke an older mdb that didn't have the latest
updates. Your other option, of course, is to go to each workstation.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 3 '08 #2

P: n/a
Well, does ADO have any wins over DAO? If it isn't really more
fabulous, then I'll stick with my ODBC version that I know inside and
out.

Thank you so much for the information.
:-)
On Nov 3, 2:51*pm, Rich P <rpng...@aol.comwrote:
Hi Laura,

Here is a code sample for using ADODB (what would be OLEDB in Access to
sql server) - make sure you have a reference in Tools\References to
Microsoft ActiveX Data Objects 2.x Library (2.5 or higher)

----------------------------------
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=YourServer;Database=YourDB;Trusted_Connecti on=Yes"

cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select * from someTbl"

Set RS = cmd.Execute

Do While Not RS.EOF
* Debug.print RS(0)
* RS.MoveNext
Loop

----------------------------------

ADODB offers a broader range of operations that can be performed (with
less restrictions like character size limits of 245 per field - not 255
- and less contention issues like record locking issues) than ODBC
between Access and sql server.

As for distribution - since an MDB is just a file - you can email the
updated file to each of your users. *The .Net environment has addressed
the issue of distribution (along with a host of other issues) and has a
feater called "Click Once" for distributing applications accross an
intranet/internet. *You can distribute pretty much anything supported by
windows (.exe, .mdb, .xls, .dll, .txt, jpg, ...). *I mention this
because distribution has always been an issue/hassel with mdb's. *The
downside of emailing mdb files is that users may end up with multiple
copies of the mdb and invoke an older mdb that didn't have the latest
updates. *Your other option, of course, is to go to each workstation.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Nov 3 '08 #3

P: n/a
Quick disclaimer: I love Access! I started my RDBMS career with Access
last century.

Multi-user environments have changed since the 16 bit days of windows
when Access reigned supreme. MS has made significant enhancements to
Access for the 32 bit systems, but has also created a whole new family
of technologies in the .Net environment rather than keep enhancing
Access. As for the enhancements in Access - the biggest one was adding
ADODB functionality for interfacing between Access and Sql Server (or
Oracle). You can use ADODB between mdb's but that is kind of overkill.
DAO is native to Jet/Access and thus easiest to use - within the Access
model. You can't use DAO directly against a sql server. There may be
tweaks that can be performed to use DAO against sql server - but that
defeats the point of BEING EASY. The main idea with any RDBMS is to
make it EASY and reliable to develop/use/and maintain. DAO is the
easiest data model to use - but only works within Access. ADODB
relieves a lot of the hassels/headaches involved with interfacing
between Access and sql server (.Net takes this part waaayyy to the next
level).

If your system/project(s) is/are fairly simple - then yes - ODBC is
easier to use than ADODB - if you are just going to read data from a sql
server. Once you start adding Insert/Edits/Delete -- good luck with
ODBC. This is where ADODB takes over - created out of necesity.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 3 '08 #4

P: n/a
I got it to connect and return records. I'm so proud! Thanks for all
your help.

This may seem like a moronic question, but is there a way I can
connect to the tables and get them to show up in the table list so I
can work with them there?

Thanks again,
Laura
On Nov 3, 4:57*pm, Rich P <rpng...@aol.comwrote:
Quick disclaimer: *I love Access! *I started my RDBMS career with Access
last century. *

Multi-user environments have changed since the 16 bit days of windows
when Access reigned supreme. *MS has made significant enhancements to
Access for the 32 bit systems, but has also created a whole new family
of technologies in the .Net environment rather than keep enhancing
Access. *As for the enhancements in Access - the biggest one was adding
ADODB functionality for interfacing between Access and Sql Server (or
Oracle). *You can use ADODB between mdb's but that is kind of overkill.
DAO is native to Jet/Access and thus easiest to use - within the Access
model. *You can't use DAO directly against a sql server. *There may be
tweaks that can be performed to use DAO against sql server - but that
defeats the point of BEING EASY. *The main idea with any RDBMS is to
make it EASY and reliable to develop/use/and maintain. *DAO is the
easiest data model to use - but only works within Access. *ADODB
relieves a lot of the hassels/headaches involved with interfacing
between Access and sql server (.Net takes this part waaayyy to the next
level).

If your system/project(s) is/are fairly simple - then yes - ODBC is
easier to use than ADODB - if you are just going to read data from a sql
server. *Once you start adding Insert/Edits/Delete -- good luck with
ODBC. *This is where ADODB takes over - created out of necesity. *

Rich

*** Sent via Developersdexhttp://www.developersdex.com***
Nov 5 '08 #5

P: n/a
Depending on what version of Access you are using (2002 or higher) you
can create an Access ADP which is basically a front end exclusively for
sql server. In the Table/Query windows you will be able to see all your
tables, views, stored procedures.

I believe ADODB was created specifically with the ADP in mind. However,
after MS got tired of wrestling with new issues that arose with the ADP
effort - they sort of abandoned the ADP in favor of .Net for sql
server/web/enterprise related operations.

Here are some caveats with the ADP - there is no DAO because the engine
the runs the ADP is actually the sql server engine - via ODBC. There in
lies some of the issues with the ADP. You manipulate the server data
with ADO but the gui is based on ODBC - a continuous connection. You
still have all the limitations of ODBC with the ADP even when using ADO.
So the mdb is actually a better front end for sql server because you can
use either ADO (ADODB) or ODBC, you aren't using both at the same time
like the ADP. So the mdb can bypass the limitations of ODBC. With the
ADP you are stuck - the trade off is that you can visually see all your
tables/view/SPs/UDFs...

The ADP was supposed to be the front end to sql server for more
sophisticated applications than what the mdb could support, but ended up
being more buggy than it was worth for the effort involved. And thus
emerged the .Net family (well, .Net emerged for a lot of reasons, but
replacing the ADP was one of them - and a fine job they did with .Net).
For sophisticated server based (enterprise) systems - with all due
respect - Access is obselete as a front end.

Your best bet if you have to stay with Access to interface with sql
server is to use ADODB with the mdb. The only caveat is that you don't
get a visual interface for looking at your tables/views... But you can
always load sql server tools (Enterprise Manager - sqlsvr2000; or SSMS -
sql server Management System - sqlsvr2005) on your workstation which
will give your the same gui as the ADP and a whole lot more tools (like
query Analyzer).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Nov 5 '08 #6

P: n/a
musicloverlch wrote:
I got it to connect and return records. I'm so proud! Thanks for all
your help.

This may seem like a moronic question, but is there a way I can
connect to the tables and get them to show up in the table list so I
can work with them there?
Yeah, use ODBC. The advantages of OLEDB and the disadvantages of ODBC are
largely imaginary. I use ODBC links against SQL Server tables heavily and
have no problems with reads, updates, inserts, or deletes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Nov 6 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.