[AccessD] OLEDB connection string - Help please

Andy Lacey andy at minstersystems.co.uk
Mon Jun 4 02:25:02 CDT 2012


Thanks so much to those trying to help me.

Version-wise I'm running A2000 under wXP. I'm going to try A2010 in a minute
but I don't expect much to change.

To summarise,because I don't want to waste anyone's time, the links to SQL
Server work fine when I use ODBC. I'm trying to use code to change the links
to OLEDB so that the app is more easily distributable. The code works and
kind of does the change but Access mysteriously adds "ODBC;" at the
beginning of the connection string. When I try to open a table I get a
"Select Data Source" popup asking for an ODBC source. Below is the full
version.

Andy

===============

Aaargh! This is driving me nuts. I've moved on and I won't bore you with the
steps and missteps but this is where I am:

1.The SQL Server db exists and I can connect to it and open tables and see
data:
- via Access with an ODBC link
- via SQL Server Management Studio

2.In both cases I can connect to the db using Windows authentication and can
open tables without being asked for any further credentials.

3.I have now got an OLEDB connection string into Access.

4.I used the .UDL method to create a connection string and put that into the
VBA code to set the connection string as "Provider=SQLOLEDB;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=TALEG_CI;Data
Source=FRxxxxxxx" where TALEG_CI is the db and FRxxxxxxx the server.

5.The Test Connection works fine on this connection.


BUT
---
6.When I look at the properties of the table in Access design mode it shows
the connection string as 3 above but with "ODBC;" added to the start of it
which is not in the string in my code (it really, really isn't). Is this
normal? Where's it coming from? Why is Access doing this? Because I suspect
this is why point 7 occurs (although I'm happy to be told otherwise).

7.When I attempt to open any of the tables up pops  a "Select Data Source"
dialog inviting me to select an ODBC source!!!!! 



I seem to be doing everything that everyone's suggested but I cannot get
this to work and am now under pressure.

Hope someone can help me crack this.

Andy













-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: 03 June 2012 18:17
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OLEDB connection string - Help please


Hi Andy:

First you can not run OLE until you have appropriate ADO drivers. They
usually look like MSADOxx.DLL and all the components will be found in a
directory like: 
C:\Program Files\Common Files\System\ado. If you find the ancient help file,
ADODC98.CHM you know you are getting warm.
...or... the ADO.Net drivers are usually built into the .Net libraries. I am
not as familiar with this as the process is usually totally automated within
VS and I am hardly a .Net guru like some of the people on this list are. 

It should be able to be run from either any version of Access or .Net. I do
not know how to test such an connection without code other than testing the
connection using the ODBC Data Source Administrator...which is site
dependant.

Have you been able to establish a connection through the ODSA? 

Once that is done, it should be really easy to do the rest. If you are
trying to get a data stream connection to, for example your latest MS SQL
server 2008, you have to make sure that MSSQL is set to accept connections
and allow access to its databases and tables...it took me over an hour, one
time to find the check boxes, through the SQL Management studio. Nothing
like that? What are you trying to connect to?

Sorry that I can not be of more help. Maybe if I know exactly what you are
trying to do, with which application, which what data server, on which OS,
in what type of environment, that might help.

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
Sent: Sunday, June 03, 2012 4:01 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OLEDB connection string - Help please

Is there any way of applying an OLEDB connection except through code?

Andy

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: 01 June 2012 17:40
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OLEDB connection string - Help please


Hi Andy:

Your ODBC file component, that you created and connected via your ODBC data
source administrator, should have a UDL extension. To expose the actual
connection string, that can be used, with the subsequent OLEDB connection,
either read the file with Notepad or change the extension to TXT.

Jim 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andrew Lacey
Sent: Friday, June 01, 2012 8:06 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] OLEDB connection string - Help please

Oh it's there alright. I'm working with it fine using ODBC. Just want to
switch to OLEDB.

Andy


On 01 June 2012 at 15:49 Jim Lawrence <accessd at shaw.ca> wrote:

> Hi Andy:
>
> Have you gone through all the basics? Sometimes the connection strings 
> are not wrong and it could just be that the connection is not working.
>
> Have you tried to "ping" or "net" the connector via IP address and 
> name,
at
> the command prompt?
>
> ping 24.123.456.5 or ping mydatabase.com ...and...
> net view and net use t: \\MyServer\MyShare ...and...
> net use s: \\MyServer\MyShare\ MyPassword /user:MyUsername 
> /persistent:no ...or...
> net use s: \\192.168.199.210\MyShare\ MyPassword /user:MyUsername 
> /persistent:no
>
> To remove test: net use s: /delete
>
> Once you are sure you can see and connect on a basic level then you 
> will feel more confident that you know you DB BE is there.
>
> HTH
> Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andrew 
> Lacey
> Sent: Friday, June 01, 2012 5:32 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] OLEDB connection string - Help please
>
> Heeeeellllllpppp please
>
>
> On 01 June 2012 at 08:17 Andrew Lacey <andy at minstersystems.co.uk> wrote:
>
> > Sorry, the error I'm getting is actually "Could not find installable
> ISAM".
> >
> > Andy
> >
> >
> > On 01 June 2012 at 08:06 Andrew Lacey <andy at minstersystems.co.uk> wrote:
> >
> > > Morning all
> > >
> > >
> > >
> > > I know I'm late to this but I'm trying to setup an OLEDB 
> > > connection
> string
> > > to
> > > SQL Server. I've found info on the web for this but I'm not having 
> > > any
> joy.
> > > This
> > > is a code snippet of what I have.
> > >
> > >
> > >
> > > strConnect = "Provider=SQLOLEDB;Data Source=FRGRAAPP02;Initial 
> > > Catalog=TALEG_CI;IntegratedSecurity=SSPI;"
> > >
> > > tdf.Connect = strConnect
> > >
> > >
> > >
> > > where tdf is defined as DAO.Tabledef and is sitting on a table
> definition of
> > > a
> > > table currently linked to an MDB. The code fails, however, with 
> > > "Error Attaching tablename". Can anyone see what I'm doing wrong?
> > > The Data Source is
the
> > > server
> > > name and TALEG_CI is the SQL Database which has no security on it.
We're
> > > wanting
> > > to use Windows authentication.
> > >
> > >
> > >
> > > Help please. I really need this.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Cheers
> > >
> > >
> > >
> > > Andy
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list