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
> Changing Connection String Via VBA, Office 2013    
 
   
fitzdesignz
post Dec 4 2018, 11:13 PM
Post#1



Posts: 33
Joined: 14-June 18



I have an excel workbook that creates a nice graphical reports from data it pulls in via a query from an MS ACCESS DB.

I would like to create the ability for the end user to change the data source where they would type the connection string into a cell and have a VBA button click injected it into the Connections string.

something like this:

CODE
Private Sub btn_ChangeConnection_click

ActiveWorkbook.Connections("ConnectionName").TextConnection.Connection = Sheets(1).Range("A1").Text
currConnection.Refresh

End Sub


Sheets(1).Range("A1") would have this:
"DSN=MS Access Database;DBQ=\\Domain\Folder\SubFolder\Live DB\AccessDB.accdb;DefaultDir=\\Domain\Folder\SubFolder\Live DB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

I have exhausted my online search for a solution and I'm hopeful that someone here can point me in the right direction.

Thanks for your help
Go to the top of the page
 
isladogs
post Dec 5 2018, 03:16 AM
Post#2



Posts: 772
Joined: 4-June 18
From: Somerset, UK


Have you looked at this link or this one.
If its not on that site I would be surprised.
This post has been edited by isladogs: Dec 5 2018, 03:20 AM
Go to the top of the page
 
fitzdesignz
post Dec 5 2018, 11:30 AM
Post#3



Posts: 33
Joined: 14-June 18




isladogs,

I have looked over those pages. I think I have the connection string written properly. I'm looking for a way to use VBA code to update it.
Go to the top of the page
 
isladogs
post Dec 5 2018, 11:48 AM
Post#4



Posts: 772
Joined: 4-June 18
From: Somerset, UK


I've not imported data from Access into Excel for over a decade.
When I need to transfer data I tend to export from Access but even that is something I now do infrequently.
As a result, others are probably better placed to advisde on the details.

Although Excel's graphing features are undoubtedly more powerful than those in Access, I find Access charts more than sufficient for almost all of my needs & I use a wide variety of charting types.
If you can do it all in Access , it would simplify your task enormously
This post has been edited by isladogs: Dec 5 2018, 12:27 PM
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 12:16 PM
Post#5


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Have you examined the connection sting in your current connection to make sure the pattern you are trying to use is exactly the same as the working one?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 12:18 PM
Post#6


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Also, I find that for trouble-shooting purposes, it is often very helpful to use variables to hold values such as that path in your example before using the variable itself in the action line.

That way you can see what is actually being sent to the connection string, and decide if it needs further tweaking. Now, you only know whether it works or doesn't work.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
fitzdesignz
post Dec 5 2018, 03:53 PM
Post#7



Posts: 33
Joined: 14-June 18



More info to add. I think I'm getting somwhere but It's racking my brain...

This code:

CODE
Sub btnChangePath_Click()
            
    ActiveWorkbook.Connections("ConnectionName").TextConnection = "DSN=MS Access Database;DBQ=\\Domain\Folder\SubFolder\Live DB\AccessDB.accdb;DefaultDir=\\Domain\Folder\SubFolder\Live DB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    ActiveWorkbook.Connections("ConnectionName").Refresh
    
  
End Sub


Throws a Run time Error 1004: Application-Defined or object-defined error"

Go to the top of the page
 
fitzdesignz
post Dec 5 2018, 05:40 PM
Post#8



Posts: 33
Joined: 14-June 18



Okay, I feel dumb.....

I found the issue.

I needed to use the .ODBCConnection.Connection reference and not .TextConnection

So as follows works beautify:

CODE
    ActiveWorkbook.Connections("ConnectionName").ODBCConnection.Connection = "ODBC;DSN=MS Access Database;DBQ=\\Domain\Folder\SubFolder\Live DB\AccessDB.accdb;DefaultDir=\\Domain\Folder\SubFolder\Live DB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
    
    ActiveWorkbook.Connections("ConnectionName").Refresh
    
End Sub
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 06:02 PM
Post#9


UA Admin
Posts: 34,077
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving the problem.

Continued success with the project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
WildBird
post Dec 5 2018, 11:06 PM
Post#10


UtterAccess VIP
Posts: 3,480
Joined: 19-August 03
From: Auckland, Little Australia


Are you saying you have end users connect to the live database with Excel to run reports?

Your DB is split with a backend and separate front end etc?

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
dflak
post Dec 6 2018, 04:33 PM
Post#11


Utter Access VIP
Posts: 6,245
Joined: 22-June 04
From: North Carolina


I know I am jumping in here late, but when I deal with connection strings, I use SPLIT to break it apart, replace the parts I want and then use Join to put it back together.


--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
dflak
post Dec 6 2018, 04:33 PM
Post#12


Utter Access VIP
Posts: 6,245
Joined: 22-June 04
From: North Carolina


I know I am jumping in here late, but when I deal with connection strings, I use SPLIT to break it apart, replace the parts I want and then use Join to put it back together.


--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2018 - 07:35 PM