Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #13377

Re: Connecting Access to a SQL Server Back End

Show all headers | View raw

Patrick Finucane brought next idea :
> On Thursday, December 13, 2018 at 4:54:45 PM UTC-5, Ron Weiner wrote:
>> Patrick Finucane submitted this idea :
>>> Hi.  When linking to a SQL Server back end, can one use the Linked Table 
>>> Manager to see what DB you are connected to?  In my case, there will be a 
>>> Production DB and a Test DB.  Can  I determine which DB I am linked to via 
>>> the Linked Table manager?  Can I link to the tables via the Linked Table 
>>> manager? 
>>> 
>>> ---
>>> This email has been checked for viruses by AVG.
>>> https://www.avg.com
>> 
>> Yes, if the databases have fifferent names as MyDtataBase and 
>> MyDataBaseTest.  If the databases have the same name but are on 
>> different servers then NO.  If production and test are on different 
>> servers you will have to write a little code.  I thing something  
>> something like this:
>> 
>>     Set tdDef = CurrentDb.TableDefs("YourLinkedTableName")
>>     Debug.Print tdDef.Properties
>> 
>> I have nothing available now to actually test this with.
>> 
>> Rdub
>
> Thanks, Ron.  Can one use the connection string in your second example to 
> figure out which DB?  I am hoping they will be on the same server (1st 
> example) but won't know until later.
>
> If I will NOT be updating or modifying tables, do I really need access to SQL 
> Management app?
>
> Any info you want to pass on to a newbie with SQL server tables?

Just tested the code I had above.  Here is code that actually works :-)

    Dim dbDef As DAO.Database
    Dim tdDef As DAO.TableDef

    Set dbDef = CurrentDb
    Set tdDef = dbDef.TableDefs("tblDays")
    Debug.Print tdDef.Connect

For my Databse this printed the Connect string:
ODBC;Description=TrackRiteDSNLess;DRIVER=SQL 
Server;SERVER=xps8700\sqlexpress08;UID=MyLogin;PWD=xxxxyyyy;APP=Microsoft® 
Access;DATABASE=TrackRite

You may not NEED the Sql tools, but once you have them you'll find 
yourself using them frequently.  I recommend you install Sql Server 
Management Studio and become at least somewhat familiar with it.  If 
ever you need to create a View or a Stored Proceedure you will 
ABSOLUTELY need it.

Rdub

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Connecting Access to a SQL Server Back End Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-12-13 13:26 -0800
  Re: Connecting Access to a SQL Server Back End Ron Weiner <rw@domain.com> - 2018-12-13 16:54 -0500
    Re: Connecting Access to a SQL Server Back End Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-12-13 14:13 -0800
      Re: Connecting Access to a SQL Server Back End Ron Weiner <rw@domain.com> - 2018-12-13 17:28 -0500
        Re: Connecting Access to a SQL Server Back End Patrick Finucane <patrickfinucanetx@gmail.com> - 2018-12-16 14:14 -0800
          Re: Connecting Access to a SQL Server Back End Ron Weiner <rw@domain.com> - 2018-12-16 22:58 -0500
      Re: Connecting Access to a SQL Server Back End "Phil Hunt" <abc@nowhere.com> - 2018-12-13 17:28 -0500

csiph-web