×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Question About Automatically Signing In to Database via Excel VBA

Question About Automatically Signing In to Database via Excel VBA

Question About Automatically Signing In to Database via Excel VBA

(OP)
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?


CODE --> vba

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

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


Sub ShowSetup()
   Dim dCount As Integer
   Dim isWBA As Boolean
   dCount = 0
   isWBA = False
   dlgSetup.Show 1 


RE: Question About Automatically Signing In to Database via Excel VBA

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)
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.

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)
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?

RE: Question About Automatically Signing In to Database via Excel VBA

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...

CODE

Sub test()
   With ActiveSheet.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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)
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?

RE: Question About Automatically Signing In to Database via Excel VBA

You will get more info after:

CODE -->

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

RE: Question About Automatically Signing In to Database via Excel VBA

Try this modification...

CODE

Sub test()
   With ActiveSheet.QueryTables(1)
      Debug.Print .Connection
   End With
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

ActiveSheet

Sorry blush

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)
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.

RE: Question About Automatically Signing In to Database via Excel VBA

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

RE: Question About Automatically Signing In to Database via Excel VBA

I originally misspelled ActiveSheet.

Plz run the original code with the correction...

CODE

Sub test()
   With ActiveSheet.ListObjects(1).QueryTable
      Debug.Print .Connection
   End With
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)
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.

RE: Question About Automatically Signing In to Database via Excel VBA

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)
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_TMI301DPTCLA_GCLA became MRSPC_TCD301DPTCLA_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,

RE: Question About Automatically Signing In to Database via Excel VBA

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...

CODE

‘
   Dim sSQL As String

   sSQL = “Select * From “ & [SelectedTable]

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


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)

Quote (SkipVought)

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.

RE: Question About Automatically Signing In to Database via Excel VBA

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question About Automatically Signing In to Database via Excel VBA

(OP)
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.





RE: Question About Automatically Signing In to Database via Excel VBA

👍

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close