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