0

I am working remotely from my customer and have an Excel spreadsheet with an SQL connection to pull data from a database. When working at home I can test it on my local test database. Everything works so I want to email the spreadsheet to my customer but need to change it to their connection string first. But when I do this it tries to refresh, fails as it cannot see their server, and the only option is to cancel which does not save the new connection string.

I have looked up answers and tried disabling refresh but cannot find a soulution. I am trying to avoid macros but will change my view if this is the best solution. screenshot of connection string set up

Ian M
  • 26
  • 4
  • The only way I can think of is to do that through VBA at opening of the workbook. – iDevlop Sep 15 '17 at 14:56
  • Thanks Patrick. I am coming to the conclusion that I need to do control the connection string from within vba. I will post a full answer when I work it out. – Ian M Sep 19 '17 at 19:24
  • 1
    Build a little function with error handling to see if \\serverX is present on the network. If it is, assign strConnection1, else assign strConnection2. – iDevlop Sep 19 '17 at 19:29

1 Answers1

0

I'm answering my only question because a few years on and I have learnt a lot. I eventually found time to get ADODB connection working in VBA. It is pretty straightforward and there are lots of answers on StackOverflow, like this one: Compiler Error: User-defined types not defined

I can build a connection string using VBA code and put the server and database name in a sheet if I want. That makes it easy to change between my test system and my client's system.

Dim Cn As ADODB.Connection 

Server_Name = Sheets("Server Connection").Range("B1") ' Enter your server name here
Database_Name = Sheets("Server Connection").Range("B2") ' Enter your database name here

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & ";Trusted_Connection=yes;"

Debug.Print SQL_string   ' SQL_string contains as string of the query I want to run
rs.Open SQL_string, Cn, adOpenStatic

' Dump to spreadsheet
With output_start        ' output_start is defined as a range where I want the output to go
     .CopyFromRecordset rs
End With

 ' Tidy up by closing down connection
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

The main thing to remember is to tick Microsoft ActiveXData Objects 2.x Library in the VBA Tools > References.

Ian M
  • 26
  • 4