[AccessD] OLEDB connection string - Help please

Stuart McLachlan stuart at lexacorp.com.pg
Mon Jun 4 05:38:55 CDT 2012


I use the Do cmd.TransferDatabase in a lot of applications.

Try

DoCmd.TransferDatabase acLink, "ODBC Database", strConn, acTable,
"FXEBCLIE", "tblFXEBCLIE", False, True 

without the dbo_

That one has bitten me a few times - Access renames the fully qualified dbo.TABLENAME to 
dbo_TABLENAME when you create a link directly using the linked table manager.  This then 
causes problems when you automate the process.  Now I always rename dbo_TABLENAME 
to just TABLENAME in the FE  after linking manually.


-- 
Stuart



On 4 Jun 2012 at 10:25, Andy Lacey wrote:

> Darren, I'm intrigued by the DoCmd.TransferDatabase acLink you use. I've
> never created a link this way. Does this create a link or refresh an
> existing one? IOW should I already link the table before running this or
> not? I'm guessing not.
> 
> Disappointingly when I run this I'm getting a message that Jet cannot find
> the SQL table so I'm doing something wrong because the table's spelt right.
> 
> Doing it this way do you avoid the user pc's having to have an ODBC data
> source setup? That's what I'm trying to avoid. I don't see any mention in
> your connection string of a data source so I'm hoping this avoids that step
> by embedding everything needed in that connection string. If only I could
> get it to work!!
> 
> Can you see anything wrong with this? I've discovered that I have to use SQL
> authentication not Windows because users are in different domains from the
> db. The user and pw are as supplied and do work when I connect manually. To
> save any risk I've replaced the server with xxxxx and the db itself with
> yyyyy. As I say this gives that Jet error saying it cannot "find the object
> dbo_FXEBCLIE".
> 
> ---------------------
> Function NewTest() 
> Dim strConn As String 
> 
> strConn = "ODBC;Description=NewFDB;Driver=SQL
> Server;SERVER=xxxxxx;APP=Microsoft Access Data
> Components;DATABASE=yyyyy;UID=FDB_GRASSE;PWD=F3d3r4l_DB;TRUSTED_CONNECTION=y
> es" 
> 
> DoCmd.TransferDatabase acLink, "ODBC Database", strConn, acTable,
> "dbo_FXEBCLIE", "tblFXEBCLIE", False, True 
> 
> End Function 
> 
> ---------------
> 
> 
> Andy
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren
> Sent: 04 June 2012 05:51
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] OLEDB connection string - Help please
> 
> 
> Hi Andy
> Play with the "Trusted Connection" value in the connection string.
> I have had this issue in the past where this annoying credential popup
> appears.
> I pass the UserName and the Password in the ConnString and have started to
> totally ignore the "TrustedConnection" setting.
> 
> Sample linking string below - watch for line wrap:
> strODBCConn = "ODBC;Description=Test Connection;DRIVER=SQL Server;SERVER=" &
> strServerIPAddress & ";APP=Microsoft Data Access Components;DATABASE=" &
> strDBName & ";UID=" & strUserID & ";PWD=" & strPassword ' '&
> ";Trusted_Connection=yes"
> 
> ...and then this to perform the link:
> DoCmd.TransferDatabase acLink, "ODBC Database", strODBCConn, acTable,
> "SomeTableInSQLServer", "tbl_TheTableNameAsIwantItInAccess", False
> 
> Also - I'll send over a sample DB I created ages ago that show these things
> in action (If I can find it).
> D
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Andy Lacey
> Sent: Monday, 4 June 2012 8:00 AM
> 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
> 

--
Stuart McLachlan

Ph:    +675 340 4392 
Mob: +675 7100 2028
Web: http://www.lexacorp.com.pg



More information about the AccessD mailing list