[AccessD] OLEDB connection string - Help please

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


Hi Jim
I have the ado folder you describe and in there I have:

Mdacreadme.htm
Adojavas.inc
Adovbs.inc
msadox.dll
Msado15.dll
Msader15.dll
Msadomd.dll
Msador15.dll
Msadrh15.dll
Msjro.dll
And a few tlb files

Does that sound ok?

Andy



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


Hi Andy:

The first question I would ask is do you have ADO installed? Without ADOs
OLEDB will not work. Which version of Access are you using? I have never had
any problems connection to a MS SQL server but then I have never made a
major Access application above A2003 so there may be other issues that I am
unaware of.

The connection strings I tend to use...similar:
"Provider=SQLOLEDB;Initial Catalog=TheClientsDatabase;Data
Source=TheClientsServer;Integrated Security=SSPI"

"Provider=SQLOLEDB;Initial Catalog=TALEG_CI;Data Source=FRxxxxxxx";
Integrated Security=SSPI;Persist Security Info=False;

The client's username and password, were already established when they
logged in and MS SQL server recognizes their access. Can ping the servers
1433 port. Does your application ask for a MS SQL server U and P at one
point or are you just using Windows integration?
  
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 3:06 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] OLEDB connection string - Help please

Does the .Attributes property come into this? Does that have to be changed
to switch from ODBC to OLEDB?

Andy



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


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