VBA to get Database name from an established ODBC connection?

B

BAlGaInTl

Well-known Member
Joined
May 7, 2003
Messages
1,082
Ok, here is the issue...

I've written an add-in that is used in a couple of locations. In each location, users have an ODBC connection to a database. The name of that ODBC connection is the same at each location.

However, the name of the actual database that it connects to varies...

For instance, in one location it is "Database"

But in another location it is "DATABASE"

The database holds the same information at both locations, but I've found my queries are case sensitive when it comes to establishing a connection.

Right now, I'm getting around it by using an inputbox, bt there has to be a better way...

Is there any way to use VBA to look at an existing ODBC connection and then get the name of the DB from there?

Any help or advice is appreciated.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
If we're talking about "Query Tables" in Excel, the sample below should spit out the individual elements of the connection string. The database name should be in there. You could use some of the VBA string functions to zero in on the DB name (usually "DB=whatever").

Hope it helps.

Gary

Code: 
Public Sub Test()

Dim iCount As Integer
Dim oQuery As QueryTable
Dim oSheet As Worksheet
Dim vSplit As Variant

For Each oSheet In ThisWorkbook.Worksheets
    For Each oQuery In oSheet.QueryTables
        vSplit = Split(oQuery.Connection, ";")
        
        For iCount = LBound(vSplit) To UBound(vSplit)
            Debug.Print vSplit(iCount)
        Next iCount
        
        Debug.Print
        
    Next oQuery
Next oSheet

End Sub
 
Upvote 0
There really are now queries in the sheet when I start.

It is a CSV spreadsheet output by a piece of equipment. I have to do some data reduction on it to make it compatible with our DB. So, I connect to the DB and perform some queries for some needed data.

The problem is with the actual connection... Now if I could just get all the DB admins to agree on a standard name for the DB, it would be fine.

:)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA - ODBC Query
Replies
0
Views
350
Mattandy55
M
K
  • Question
How to Use "GetPivotData" formula to retrieve data from a closed workbook
Replies
3
Views
303
RoryA
RoryA
S
  • Question
Experimenting with ODBC DSN to import Excel Data from another Workbook
Replies
0
Views
372
sspatriots
S
S
  • Solved
Accessing Sharepoint List from Excel/VBA using ADODB connection
Replies
3
Views
3K
RoryA
RoryA
C
  • Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
313
Clif McIrvin
C

Forum statistics

Threads
1,215,004
Messages
6,122,656
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top