Question About Automatically Signing In to Database via Excel VBA 1

[kc27]

I have a database username and password written into the VBA code of an Excel tool that is used to generate reports. The correct functionality of the form is that the user clicks a button and begins making criteria selections to generate a report. To generate the report, the Excel tool needs to read a database. With the way the Excel VBA is written, the user should already be signed in to the database via the VBA in the Excel document. You can see below in the code excerpt the green highlighting shows the user name and password hard coded into the form, and then yellow highlighting shows the action "ShowSetup()" that initiates when the user clicks the button.

However, once clicked, the button on the form displays the user name and password entry box shown below. That is not correct, it should show the user a report criteria selection screen. Can anyone explain what would cause the hard coded database credentials to be ignored?

Public Const DB_ATTR = "I:\sysfiles\mg_data\CSDB2.mdb"
Public DBAT As DAO.Database
Public DB2 As Object
Public WBA As Workbook
Public WSGC As Worksheet, WSGA As Worksheet, WSGI As Worksheet, WSAC As Worksheet

Public deptArr()

Public Const c_PD1 = 3
Public Const c_PD2 = 7
Public Const c_PD3 = 11
Public Const c_LAST = 14

[highlight #8AE234]Public Sub DB2Connect()
    uid = "useridhere"
    pwd = "passwordhere"
    strConnect = UCase(DBAT.TableDefs("MRSPC_TCD301DPTCLA_GCLA").Connect) & ";UID=" & uid & ";PWD=" & pwd
    Set DB2 = OpenDatabase("", False, False, strConnect)
    strConnect = UCase(DBAT.TableDefs("MRSPC_TCD300GEN_CLASS").Connect) & ";UID=" & uid & ";PWD=" & pwd
    Set DB2 = OpenDatabase("", False, False, strConnect)
    DB2.Close: Set DB2 = Nothing
End Sub[/highlight]


Sub [highlight #FCE94F]ShowSetup()[/highlight]
   Dim dCount As Integer
   Dim isWBA As Boolean
   dCount = 0
   isWBA = False
   dlgSetup.Show 1

 

[SkipVought]

Hi,

Don’t understand the green code where you assign the DB2 object 1) first to one db, 2) then another, 3) then close???



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

I did not create the tool. I do not understand the logic behind connecting to those two tables then closing. Might be needed for the criteria selection that occurs next. It did work properly until table names were changed.

 

[kc27]

If you look in the screen shot of the "Connect to DB2 database" dialogue box, there is a value pre-populated for Database Alias. What controls the database alias that is displayed? There are several other databases available in that drop-down menu. I do not see any references to IQPROD in the Excel files VBA code that tell it DB2 = IQPROD. Is there a method that the author of the tool used that would not be part of the VBA code?

 

[SkipVought]

The ODBC connection to the DB2 database, had been configured by someone, perhaps your IT.

What I’d suggest is this temporary process, to determine how the green code ought to be...

On a new sheet, use Data > Get External Date > From Other Sources > From Microsoft Query... and select the appropriate DB2 db. This will create a QueryTable object on your sheet.

alt-F11, toggles between the sheet and VBA editor.

In the VBA editor paste this code...

Sub test()
   With [b]ActiveSheet[/b].ListObjects(1).QueryTable
      Debug.Print .Connection
   End With
End Sub

RUN

View > Immediate Window

In the Immediate window, will be displayed the Connection string for that db. Plz post back with this info.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Hi Skip

Thanks for your help on this. I put a query on a blank sheet and loaded a table

I get an "object required" message when I run the code. The query object is already on the document and displaying the table data. Do I have to do something else to get the the code to recognize the object?

Do I need to edit your code with the name of the object?

 

[combo]

You will get more info after:

Sub test
Dim wks As Worksheet
Set wks = ActiveSheet
Stop
End Sub

Your code should break. Now in VBE open "Locals" window (Viev > Locals window) and examine the "wks" tree. Check name, go to ListObjects coccection, expand Item 1, expand QueryTable, you should see all available properties of the tree objects.

combo

 

[SkipVought]

Try this modification...

Sub test()
   With [b]ActiveSheet[/b].QueryTables(1)
      Debug.Print .Connection
   End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

ActiveSheet

Sorry

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Maybe I am missing a step that both of you assume that I would be doing. Here is what I did.

1. Open blank Excel document
2. Clicked on the Data tab
3. Get Data > From Other Sources > From Microsoft Query
4. Choose the table I want and choose "Return data to Microsoft Excel"
5. Table data is displayed in Excel
6. Alt + F11
7. Paste Skip's modified code posted on 8 Feb 18 16:37
8. Get Run-time error 9: 'Subscript out of range'

Combo, I used your code and navigated to Names > Parent > ListObjects > Item 1 > QueryTable and see the properties. Just not sure what do with them.

 

[combo]

See Connection. You are digging the worksheet object model, the vindow displays most of properties. Just make sure that ActiveSheet returns the Worksheet you need, ListObjects(1) is the table you need etc. If possible, check Name property (if exists)for examined objects, it's helpful.


combo

 

[SkipVought]

I originally misspelled ActiveSheet.

Plz run the original code with the correction...

Sub test()
   With [b]ActiveSheet[/b].ListObjects(1).QueryTable
      Debug.Print .Connection
   End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Thanks - below is what appears in the intermediate window:

ODBC;DSN=PROD;UID=CSSSXLSP;;MODE=SHARE;DBALIAS=PROD;TXNISOLATION=1;

Interesting that the "Connect to DB2 database" dialogue box that I included in my first post shows a db alias of "IQPROD" and not "PROD" that resulted from the query.

 

[SkipVought]

Well, now you know what the system needs to “see.”

Keep this tool handy in your toolbox. You can also see the .CommandText (SQL) or assign different SQL on the fly, which is what you appear you intend to do. I did this regularly with Oracle, DB2, SQL Server, Access, Excel, Text db sources to bring data into Excel for analysis & reporting.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

Not sure what you mean by what the system needs to see.

My only modification to the VBA code in the tool was changing table names. So a table that was MRSPC_[highlight #FCE94F]TMI[/highlight]301DPTCLA_GCLA became MRSPC_[highlight #FCE94F]TCD[/highlight]301DPTCLA_GCLA. That was it. Prior to that modification, the user would click on a button in the Excel document, and it would provide them with a criteria selection screen. After the modifications, the "Connect to DB2 database" dialogue box displays,

 

[SkipVought]

I assume that both tables mentioned in your last post are in the Prod db.

Let’s suppose that you begin with the new sheet with the QueryTable that has a connection to the DB2 table.

You ought to be able to do something like this. If the user selection were a Data > Validation ...in-cell Drop Down in a Named Range cell named SelectedTable...

‘
   Dim sSQL As String

   sSQL = “Select * From “ & [[b]SelectedTable[/b]]

   With ActiveSheet.ListObjects(1).QueryTable
      .CommandText = sSQL
      .Refresh False
   End With

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

I assume that both tables mentioned in your last post are in the Prod db.

I need to check with a database administrator. IQPROD had the TMI tables; PROD has the TDC tables, at least that is what I believe is the case. Once I find out I will post back here.

 

[SkipVought]

If you have a database other than PROD, then you must FIRST configure an ODBC driver for that db, using the ODBC Data Source Administrator (either 32-bit or 64-bit)

After configuring a driver for IQPROD, you can programatically switch databases and SQL in this QueryTable.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kc27]

I want to follow up on this thread:

A little background. The Excel tool pulls data from an Access database. The Access database contains no native Access tables. it only has linked tables. The tables in the originating database had been modified (new names, or they were new tables altogether), so I needed to create new links in the Access database. I also needed to update the table names in the VBA code of the Excel tool.

The update of the VBA code in the Excel tool went fine. When I created the new links in the Access database, I used a different DSN because I thought the original DSN was obsolete. With the new DSN, even though the user name and password were hard coded in the VBA code, I got a password dialogue entry box when running the form. Rebuilding the Access database and creating the linked tables using the original DSN resolved the problem. Maybe the user name and password were not valid with the second DSN, and that is why the password dialogue box appeared.

Thanks to everyone for the help, and thanks to SkipVought for the troubleshooting code.

 

[SkipVought]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]