Connect to SQL Database

[johnisotank]

Hi,

hoping someone could help me connect to my database.

At the moment I have a spreadsheet that connects fine (I have the connection in my ODBC data sources). Problem is that the spreadsheet will be used by many different people and that will involve adding the connection into their ODBC data sources. I would really like to avoid this because it will mean I need to go round everyones PC and add it and if I was to change the SQL password, I'd have to do it all again.

I was hoping someone could tell me how to connect to the database using VB so I only need edit that code instead of everyones data sources..

I am using Excel 2003 and SQL Server 2005 Express.

Thanks
John

 

[SkipVought]

Hi,

Each person running the query will STILL need a driver configured for the SQL Server, regardless if you use MS Query or ADODB via VBA. I have the same issue. You just suck it up and help each new user configure their driver, and set the object reference in the VB editor for ADO.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnisotank]

Hi Skip, it wasn't the driver I was worried about installing (all modern PC's have the SQL Server driver don't they - well at least the few I have tried) it was the data source.

I have now managed to get it going using this code (Works on any PC regardless of what data sources are installed.

Dim qt As QueryTable
 connstring = "ODBC;Driver={SQL Server};Server=128.128.128.1\SQLEXPRESS;Database=DeltaRE;Uid=sa;Pwd=MyPassword"
     With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A9"), Sql:=sSQL)
        .Refresh
    End With

I am now left with one problem....

I have some values in the range A1:C6. I have told the QueryTable to go into cell A9 but when I do the refresh, it deletes all the values from A1:C6 (but does put the query table in A9.

Any chance you know the answer to this?

Thanks
John

 

[SkipVought]

"...need a driver configured ..."

Yes, they all probably DO have drivers, but NONE of the drivers are CONFIGURED.

Yes, MS Query seems to shift data, in cells above the querytable, to the right.

You can counter that by either storing the data in that range and replacing after the refresh, by using ADO instead of MS Query or by doing your MS Query on a separate sheet and copying the resultset to desired destination after the refresh.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[johnisotank]

Hi Skip, thanks for the driver clarification.

I didn't even notice that the data had been moved to the right (instead of my first thought, removed completely).

I've decided to simply clear the contents of the range where the querytable appears prior to refreshing it and it is working superb now.

think Im done with this part now. thanks so much for your help on this and other posts.

John