UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Change Dsn Only In Odbc Connection String Using Vba, Access 2013    
 
   
catbeasy
post May 16 2019, 01:04 PM
Post#1



Posts: 477
Joined: 14-March 05



So I understand how to change a whole connection string for a series of ODBC linked tables. However, the only thing i want to change in the connection string is the DSN and description (they in this instance actually having the same value), i don't need to change the whole thing (which would require fiddling with making sure each table name is also changed in the connection string - which can be done, but you know, Occam's razor and all that..)

So the connection string is as:

ODBC;DSN=AB_300;Description=AB_300;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=AB;;TABLE=dbo.Dim_MyTable1

Is there a way to just update the DSN = and Description =

Or do i have to do the whole string?

Thanks for any help!





Go to the top of the page
 
theDBguy
post May 16 2019, 01:14 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,279
Joined: 19-June 07
From: SunnySandyEggo


Hi. I think so. Check out the Split() and the Replace() functions.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
catbeasy
post May 16 2019, 03:44 PM
Post#3



Posts: 477
Joined: 14-March 05



ok, will do, think i know what to do, but it seems like it would be the same trouble as changing the table name each iteration through..however, i know who to do this latter. The former you are talking about i haven't done, so may be a good learning exercise as well..

Cheers!
Go to the top of the page
 
theDBguy
post May 16 2019, 03:47 PM
Post#4


Access Wiki and Forums Moderator
Posts: 75,279
Joined: 19-June 07
From: SunnySandyEggo


Just give it a shot and let us know how it goes...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post May 16 2019, 04:48 PM
Post#5


UtterAccess VIP
Posts: 4,535
Joined: 5-June 07
From: UK


I am not sure you can edit an ODBC connect string. When I have assembled an ODBC connection, the bits I enter don't look like the string.

If you can it should be straightforward.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
PaulBrand
post May 17 2019, 05:27 AM
Post#6



Posts: 1,703
Joined: 4-September 02
From: Oxford UK


I normally declare connection parts as variables:

CODE
Dim strDSN As String
Dim strDesc As String

strDSN = "AB_300"
strDesc = "AB_300"

ODBC;DSN=strDSN;Description=strDesc;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=AB;;TABLE=dbo.Dim_MyTable1


Easy to change then...

--------------------
Paul
Go to the top of the page
 
gemmathehusky
post May 17 2019, 05:45 AM
Post#7


UtterAccess VIP
Posts: 4,535
Joined: 5-June 07
From: UK


I think the question is

is editing the tabledef.connect property sufficient, or do you have to drop the connection, and create a new one.
And can you just assign a tabeldef.connect value as a ready formed string

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
catbeasy
post May 17 2019, 12:43 PM
Post#8



Posts: 477
Joined: 14-March 05



So, i tried this and it worked. All the tables were connected to BA800 and now they are connected to BA900. They are all SQL Server tables. I added a record via Management Studio directly in SQL and it shows up in the Access linked table. So, the connection is good. However, the reason i wanted to do it this way was to avoid issues with Primary Keys linking 'manually'. You have to assign PK's manually for some tables that don't have them in SQL and rather than go through the rigormaroll of finding out which ones need PK's and what the keys are, it was easier to keep everything 'pat' and just change the connection string..However..!

There might be some issues, as i'm still having issues that were caused by this PK issue when i did this another time (manually not programatically - where i added the PK's to the tables as i linked them as i was 'asked' by Access). So, i have a bit more research to do. The problem could be elsewhere- i don't know..maybe with this new server. But the issue here is that the DB worked fine - updating, inserting records on the current server. When i ran the below code, it did change the server connection to the new one, but now the DB doesn't work. That seems like it shouldn't be. So i'm going to check to see if maybe when they xfrd the tables to the new server, maybe they screwed something up on one of the tables that's causing it to balk - maybe one or more tables didn't have their PK propagate over from the current Server/DB/Tables to the new Server/DB/Tables. They should be the same..but..?

Anyway, thanks for all the comments. At the least, the below does iterate through all the tables I need changing the connection for that I've got inserted into "00_tbl_name". It then calls a function that gets the connection string for that name and then does the change - 'replace' just the current server to the new server..a lot less than i thought i would have to do..Cheers to DBGuy for the tip!



CODE
Sub ChangeODBCConnectionString()


'to simply change a connection string:
Dim db As DAO.Database
Set db = CurrentDb
Dim tdf As DAO.TableDef
Dim strTblName As String
Dim rs As Recordset
Dim strConString As String 'connection string


Set db = CurrentDb
Set rs = db.OpenRecordset("00_tbl_name", dbOpenDynaset)

Dim strOrigConString As String
Dim strNewConString As String

rs.MoveFirst

Do Until rs.EOF = True

    'set the tablename..
    strTblName = rs!TableName
    
    'get the current connection string..
    strOrigConString = GetLinkedDBName(strTblName)
    
    'replace the dsn server part..
    strNewConString = Replace(strOrigConString, "BA_800", "BA_900")
    
    Set tdf = db.TableDefs(strTblName)
    tdf.Connect = strNewConString
    tdf.RefreshLink
    

    rs.MoveNext
    
    
Loop
Go to the top of the page
 
theDBguy
post May 17 2019, 04:34 PM
Post#9


Access Wiki and Forums Moderator
Posts: 75,279
Joined: 19-June 07
From: SunnySandyEggo


Hi. Congratulations! Glad to hear you got part of it sorted out. Good luck with the rest of your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th May 2019 - 07:46 PM