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