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

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

Re: Connecting Access to a SQL Server Back End

Show all headers | View raw

Patrick Finucane laid this down on his screen :
> On Thursday, December 13, 2018 at 5:28:30 PM UTC-5, Ron Weiner wrote:
>> 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
>
> Ron, when I have the Test.db as the data source, and I now want to distribute 
> the app, I want the data source to be Production.db.  What method do you use 
> to swap the names of the backend db?
>
Well I knew that you would eventually ask this question, and sadly the 
answer is semi complex.  First a little history.  The folks I worked 
for had dozens of more or less identical Sql databases in dozens of 
locations.  The software I wrote needed to be able to switch among them 
with unskilled users that only needed to have limited information, 
(Server Name amd Database name).

 I called the process “Sql Linker” and the form  “frmLinker” had a pair 
of text boxes and a couple buttons.  The first time the linker form was 
used both Text boxes would be empty and the user was required to 
provide (a) the Server Name and (b) the database name.  At the end of a 
successful link the form would persist these two settings to an Access 
table in the front end DB.

When the Command button was pressed the form would confirm that it 
could connect to the Sql Server specified and if it could would one at 
a time delete all of the Sql Linked tables.  Then it would re-link all 
of the Sql tables.  Easy Peasy.  The whole process would only take a 
few seconds to link many dozens of Sql tables if the server was on a 
local Lan.  No more than a minute even if the server was in the Cloud.

The other Button was Cancel and just closed the form

One other thing to consider in your solution is that any time that a 
schema change is made to a linked Sql table, or a new Sql table is 
added, you'll need to re-link the Changed/New table to keep Access up 
to date. 0 The way I handled this was to run the Sql Linker and re-link 
em all.

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