INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

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!

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

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...I have to add my thanks and appreciation for your wonderful site... People who frequent the site are the two best things - nice and smart..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Enterprise Applications > ERP and CRM Solution Vendors > Lilly Software Associates VISUAL solutions Forum

VBA communication with Visual Database

CODE

Dim cn As New ADODB.Connection

    With cn
        .Provider = "Oraoledb.Oracle"
        .ConnectionString = "Data Source=VMFG;User ID=username;password=password"
        .Open
    End With

    Dim rs As New ADODB.Recordset

    rs.Open "select * from part", cn, 3, 3

    'Handle recordset like normal.

Let me know if this helps.

Cassidy

NBVC (TechnicalUser)

12 Oct 06 8:25

Hi Cassidy,

Thanks for the reply.  I actually ended up recording a macro that performs the query (pretty much as you had suggested in the first part).

Now my problem is that I have several buttons on my summary excel sheet.  Each button performs a different query on a separate sheet and returns specific info to the summary sheet.

I have managed to get each button to call one subroutine to perform the various queries, the problem is the user must re-enter his/her userid and password for each query.  I am trying to figure out how I can just get the user to enter the user info once only (the first time they open the sheet or the first button they click).

Here is the code to process each button:

CODE

Sub ProcessButton()
    'This subroutine is called everytime an audit button is clicked and the corresponding query is performed

    Application.ScreenUpdating = False

    Range("C" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select 'Triggers hyperlink located in Column C of Row where the button is located
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 'Activates Hyperlink to appropriate sheet
    Range("A2").Select 'Activates a cell within query in order to refresh the query
    Selection.QueryTable.Refresh BackgroundQuery:=False 'refresh query
    Call ButtonHome 'goes back to home page
    Range("D" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select 'activates formula cell in main sheet to return desired results
    'Displays result in large red font
    With Selection.Font
        .Name = "Arial"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
    End With
    Range("F" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select 'activates time stamp cell
    Selection.Font.ColorIndex = 1 'sets font to normal
    Range("F" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row) = Date & " " & Time 'date and time stamps the query
    Range("D" & ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row).Select 'pauses on results cell

    Application.ScreenUpdating = True

End Sub

Here is the code I have so far to get access the database and get user input.

CODE

Sub GetAllQueries()

Dim User As Variant, Password As Variant, database As Variant

database = InputBox("Database?", "Database")
ODBC = database & "_ODBC"
User = InputBox("User ID?", "UserId")
Password = InputBox("Password", "Password")

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=" & ODBC & ";UID=" & User & ";Pwd=" & Password & ";DBQ=" & database & ";DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=F;BAM=IfAllSuccess" _
        ), Array("ful;MTS=F;MDI=F;CSR=F;FWC=F;PFC=10;TLO=0;")), Destination:=Range( _
        "A1"))
        .CommandText = Array( _
        "SELECT PART.ID, PART.FABRICATED" & Chr(13) & "" & Chr(10) & "FROM SYSADM.PART PART" & Chr(13) & "" & Chr(10) & "WHERE (PART.FABRICATED='Y') AND (PART.QTY_ON_HAND>100)" _
        )
        .Name = "Query from" & " &ODBC& "
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        '.RefreshStyle = xlInsertDeleteCells
        .RefreshStyle = xlDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Looking forward to any help/suggestions you can offer.

CassidyHunt (IS/IT--Management)

12 Oct 06 10:16

First Question I have is why would you use ODBC over OLEDB? The ODBC driver is going to force you to enter a user name and password everytime unless you statically assign that. At least that has always been my case. If it were me I would modifiy the connection to be OLEDB and possibly think about using a static username and password.

OLEDB is going to increase your speed and give you way better results I think.

Here is the code I came up with that only requires you to enter a the input data once and it remembers it for the length of hte application.

CODE

Public username As String
Public password As String
Public database As String
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2006 by Cassidy Hunt
'

'
GetAllQueries
End Sub
Public Sub GetAllQueries()

If IsNull(username) = True Or username = "" Then
    database = InputBox("Database?", "Database")
    username = InputBox("User ID?", "UserId")
    password = InputBox("Password", "Password")
End If

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

With cn
    .Provider = "Oraoledb.Oracle"
    .ConnectionString = "Data Source=" & database & ";User ID=" & username & ";password=" & password
    .Open
End With

rs.Open "select id, fabricated from part where fabricated = 'Y' and qty_on_hand >= 10", cn, 3, 3

With ActiveSheet.QueryTables.Add( _
        Connection:=rs, _
        Destination:=Range("A1"))
    .Name = "Query from" & " &database& "
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    '.RefreshStyle = xlInsertDeleteCells
    .RefreshStyle = xlDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With

Set cn = Nothing
Set rs = Nothing
End Sub

As you can see I put some global scoped variables at above all my sub routines to store my database, username, and password information. Then I just simply check to see if that information has been completed before prompting for it. That will eliminate the excel side of the login.

Then you can see I switched your ODBC information for, at least from my viewpoint, easier OLEDB code.

The rest of the code is pretty much yours. Another option that really makes this powerful would be to utilize a webservice and call that from the excel page. Then you could leverage more flexablility, speed, and benefits of running VB.Net compiled code.

Let me knwo if you need help implementing.

Cassidy

NBVC (TechnicalUser)

12 Oct 06 13:11

Hi again Cassidy and thanks for sticking with me :)

First of all, I am just a novice with VBA itself, let alone Database connectivity via VBA code...As you can tell, I just recorded the query to get my macro. I am not against making it more efficient as you suggest with the Oledb connection.

Well, I copied and pasted your macro and got a "user-defined type not defined" error on the "Dim cn As New ADODB.Connection" line. I put this macro in a new module. Do I need to do anything first?

Also, my buttons on the summary page actually emulate the "Refresh Query" function for different queries on different sheets. I am not coding the Sql statements within the macro...that one above was just so I can get a recorded macor for accessing the Database.....

So what I really need is that the GetAllQueries() macro be used only to get the user input initially and store the info so that my ProcessButton() macro above can keep referencing that info instead of asking for the userid info again and again.

Maybe I don't really need a second macro, just need to incorporate it into my ProcessButton() macro somehow.

Possible?

CassidyHunt (IS/IT--Management)

12 Oct 06 13:54

Well you need to be in the Microsoft Visual Basic window where you edit your macro's.  Go to tools and click References.  Scroll through the list until you see this entry:

Microsoft ActiveX Data Objects 2.5 Library

Now you might have several ranging from 2.0 to 2.8 depending on how up to date you are on your data access objects.  You need to select at least 2.5 because the older ones seem to have bugs to me.

Well using variables at the global level of the application above will allow you to keep the user information while they have excel open.

Where are you getting the SQL from?

If you are using the query wizard for external data then you should be able to use the Refresh data option from the data pull down menu in Excel and it will refresh everything in the spreadsheet. If you want to automate that with a macro that stores user information just edit the macro and replace the user information with global variables like above.  Then you only have to prompt once and let he macro do the rest.

I guess what I am really getting at is what is the end result?  Typically when I setup something like this it is so I can feel say sheet1 with data then drive pivot tables or formulas in the rest of the sheet.

Let me know and I will see what I can do.

Cassidy

NBVC (TechnicalUser)

12 Oct 06 14:46

Hi Cassidy,

I added the reference and your macro worked. Thanks.

Now I am trying to understand how to incorporate that into my code below:

CODE

This macro was basically recorded as you had said. I just used the query wizard and refreshed with all the sign-in inputs. But that info didn't get put into code.

How do I incorporate you macro into this one to get the user input once only.

Remember that this same macro gets called by many different buttons. Depending on where the button is, it processes the corresponding query refresh.

CassidyHunt (IS/IT--Management)

12 Oct 06 15:12

Although I don't have any way to test this you should be able to modify the initial attempt to get the data to save the password using a parameter string like this in your querytable object call:

CODE

.Name = strQryTableName
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False

Notice the .SavePassword and .SaveData fields. Those are only available during the time the Querytable is made. AFter that it ends up in the backgroundquery object and I have never tried to access it from there. This would probably require you to move to a single username and password to access the data. If the data is user specific then you will probably need to build your own refresh routine. Basically call the routine I wrote above with the SQL in it that refreshes everytime based on the critieria.

Let me know if that makes sense.

Cassidy

NBVC (TechnicalUser)

12 Oct 06 15:29

I think maybe I am just not understanding it properly.

If I go to a sheet that already has a query run (done previously through query wizard), and I start to record a macro to simply refresh the query, I get the following:

CODE

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 10/12/2006 by NBVC
'

'
Range("E18").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub

even though I was asked for username and password, it didn't record that information in the macro. Why not?

I have other queries in other sheets too. I want to access them all, one at a time without re-entering the user id and password.

CassidyHunt (IS/IT--Management)

12 Oct 06 15:45

The prompt you have for a username and password wasn't something excel generated.  Windows and the ODBC driver software generated that prompt.  The only way around it that I am aware of is to save the password in the connection string at design time of the query, build your own query and refresh engine, or figure out the how the backgroundquery object works.  From what I see on the backgroundquery object I couldn't find a way to modify the query to contain the password after the query has been called.

Look at it like this.  (Keep in mind this is not the way programmers look at this but it illustrates my point)

Before when you ran the code I gave you, you found out you were missing the Microsoft ActiveX 2.5 Data Object library.  Think of that library as a program all by itself.  Similiar to Excel but without an interface.  Now you set different prefrences inside that program like connection string, sql query, etc..  Then you give it the go ahead to do something.  It returns, in our case, a recordset.  Now you take that recordset and use an excel function to extract that data and put it in Excel.

The connection you have above is ODBC which is another program that goes out and does realitivly the same thing.  That dialog box is coming from that program.

So modifying has to be done during the initial setup, because after that a copy of what was done is placed in a readonly object, as far as I can tell, called backgroundquery.

Let me know if that makes sense.

Cassidy

NBVC (TechnicalUser)

12 Oct 06 15:54

Okay, Thanks Cassidy for your help and explanation.

I will have sit on this one and really try to understand the whole concept.

I hope that I can come back with any further questions.

CassidyHunt (IS/IT--Management)

12 Oct 06 16:05

I think you will have better results for what you are doing using something like ReportViewer and the excel export tool it has. The software is free and tons of tutorials on making it work are available.

Feel free to come back with any questions you have. Like I said I do not really have a great grasp on the backgroundquery object so there might be a workaround. I would like to know that outcome as well.

Thanks

Cassidy

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!

Back To Forum

Back To Lilly Software Associates VISUAL solutions

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

VBA communication with Visual Database

CODE

CODE

CODE

CODE

CODE

CODE

CODE

Reply To This Thread

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

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!