Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


VBA Set ODBC Connection String For Query

My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?

The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.

Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
ODBC Connection To An Oracle Database
I am currently querying data from an Oracle database through MS Access and then passing it to Excel for the user to work. Not all users have MS Access so I would like to automate the data pull from Excel

Database information:

The DNS is: a150
The Tables are:


Sample SQL string would be:

View Replies!   View Related
Running Access Query In Excel And Connection
I am not sure how to setup the initial connection to the datbase from excel and was wondering what would be the best way to go about things. Is it possible to run the query stored in the database and pass it a value or would I be better off just coding it into a sqlstring within the macro.

Here is the query that is in the database...when run it asks for the "Status?" variable.

SELECT ordernumber, mobilenumber
FROM bookings
WHERE status = [status?];

So yes, really simple query but yeah just want to know how to pass it that variable from excel or can I just set it up in the actual macro to maybe read the status from a cell?

View Replies!   View Related
Pivot Refresh After ODBC Query Complete Refresh
I have a query with ODBC connection to a SQL database. This query's parameter is linked to a cell. The resultant data is the source for a pivot table. I want to refresh the pivot table, when the query is run. I've tried using the cell that triggers the query....but the problem is that the query takes about 10 seconds to run. By the time the query returns new data....the pivot has already refreshed. I need it to refresh AFTER the query is complete.

I tried adding a cell that sums up the data from the query...thinking when THAT changes (due to updated data), to trigger the pivot refresh. Problem is that I don't know the trigger for when the sum cell changes (ie....formula change, not typed in.)

View Replies!   View Related
Numeric String Manipulation Query
I have a sizable spreadsheet, which has one column where the cells are mostly numeric strings of up to 6 numbers - there are variations, which I will mention.

What I need to do, is to treat the numbers as individual figures, and add them, then divide by the number of figures. e.g. cell value '123456' I need to process as (1+2+3+4+5+6)/6.

The variations are that '0' has to be added as 10, and any letters need to be added as 12. '-' and '/' which are the only non-alphanumeric values should be ignored.

View Replies!   View Related
Find Text String In Web Query
I want to find the number within this webpage and report it to a Cell in excel....

View Replies!   View Related
Query Parameters: Editing The Middle Of A String
My question is in regards to query and setting up parameters. I have the following query (I am using an example for security purposes)****&afilter=A&exact=False" _

What I am trying to do is have a parameter for the user to change the AA21. For example the user could type AA33 and it would change the code to:****&afilter=A&exact=False" _

I am familiar with creating parameters and have been able to add things to the end of the string but not in the middle. I have been trying a very long time to figure this out.

View Replies!   View Related
Mixing ADO And ODBC
I've got an Excel document I've built which uses multiple ODBC QueryTables

View Replies!   View Related
Connect To Access Using ODBC
how to do this with Java but not VBA, I have the capability of understanding it I just need to know where to look because I can't seem to find any solid information on the net from my searches. Does anyone have any websites, other threads or anything they could direct me to for this kind of thing.

I was contemplating putting this in the Access thread but I want to connect to Access through Excel so I think it fits both.

My plan is to use a macro to send specific cells to five seperate tables in an Access database using VBA and SQL.

View Replies!   View Related
API Call To Get List Of ODBC DSNs
Does anyone have an API that will allow me to get a list of ODBC data sources defined in the Windows ODBC Control Panel?

View Replies!   View Related
Index/match/vlookup And ODBC SQL
I want to create a formula that will use a database table as a lookup for index/match and vlookup type functions.

I have my ODBC connection connecting to a mysql database and am successfully able to run sql queries through excel.

Is there a way to get index/match/vlookup formulas to use the database table as the lookup range?

View Replies!   View Related
ODBC Lookup To Populate Variable
I have no problem setting ODBC connections in vba, that return the results to a selected Range destination, but short of then setting txtVariable = Range("A1") is there any way to directly tell the ODBC connection to set the result of the query to txtVariable?

View Replies!   View Related
Connecting To A Database Without The Local Machines Odbc
I'm working on a spreadsheet that will connect to a firebird DB (Programmed using VBA and ADO intially).. The spreadsheet itself will reside on a server in a shared folder..

When a user opens the file up, I'd like for the spreadsheet to use the servers ODBC connection, and not the client machine.. This way we don't have to install the Firebird ODBC driver on each client workstation..

View Replies!   View Related
ODBC :: Importing External Data From An Oracle 9i Database
I'm having some issues importing external data from an Oracle 9i database.
Here is what is happening:

I am initially able to import data from the table I want to into Excel.
I do this through "Data -> Import External Data -> New Database Query" where I have my .ORA data source. I'm able to log in using my user and password and import the table. My problems come after I import this first set of data.

If I try to "Edit Query…" I get an error box that says "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

If I try to "Refresh Data" I get two error boxes. The first is just: "[Microsoft][ODBC driver for Oracle][Oracle]" and the next says: "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed". It then prompts me for the User name and password of the DB I'm connecting and after I enter the information, I get the same two error messages.

Also, if I attempt to establish another connection following the "Data -> Import External Data…" steps I run into a different problem. Upon entering the information for the User Name and password prompt I get two error messages. One says "!" and the next box says "Couldn't read this file". Once I click "OK" in those boxes, the Microsoft Query window automatically opens with nothing in it.

I can't seem to figure out why I can initially pull down data and then cannot succeed afterwards. I'm unable to connect to the DB I have through Excel until I completely closed all Microsoft Excel instances.

I also use this data source in Microsoft Access without any problems.

View Replies!   View Related
DBGrid Connection Through ADO (SQL)
to retrieve the data from MS access table in to the DB Grid at user form through MS SQL Query.

I am trying to use ADO for the same.

View Replies!   View Related
Workbook Connection
i have two workbooks that use a identical worksheet which is called products (this sheet contains rows of over 19000 products ). The problem is i cannot merge the two workbooks together because they are just too big in size.

Also these workbooks have similar userforms and macros that rely on this product databases (worksheets).

My problem is when i add a new product to of one product databases i have to also add it to the second product database again. Is there a way where i can update both worksheets together without opening both workbooks.

I have three userforms that rely on this worksheet from both workbooks:

frmNewProduct - To add a new product to the worksheet.

frmProductUpdate - To edit, replace or delete products in the worksheet.

frmSearch - To search for products inside the worksheet.

I have also got other define names, worksheets and formulas that rely on these product database worksheets.

View Replies!   View Related
SAP Connection - Which References
I'm trying to connect to SAP R/3, and I think I'm not sure which references (Tools --> References) I'm supposed have checked.

All I want to do for now is the following:

Dim sapConn As Object
Set sapConn = CreateObject("SAP. Functions")

Unfortunately, I am getting a "Run-time error '-2147024770 (8007007e)': Automation error. The specified module could not be found."

I have read somewhere random on the internet that I should add "SAP:Remote Function Call: COM Support 1.0 Type Library" which links to the SAPGui Provided library librfc32.dll. However, this alone doesn't seem to work...

View Replies!   View Related
DB Connection Lockout Other DB Connections
I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.

first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?

second question: if so, is there a setting or something that can make excel more "passive"?

View Replies!   View Related
Can't Remove Public ADO Connection
I have a VBA application that uses data from a sheet in the excel workbook. It accesses this data with an ADO connection. On workbook open I open this connection--I declare it as public variable so I can continue to use it throughout the session. My problem is that the connection really doesn't ever dissappear. I have the problem that once I close the workbook, the project still exists in the VB Editor. How do I get rid of the connection? I am stuck because I declared it as public? I want to keep it open, rather than opening every time I want to use it, because it is faster to keep the connection open.

My code is below.

Option Explicit 'requires variable declaration
'declare global connection
Public cn As ADODB.Connection

Sub OpenDBConnection()
'open db connection
'this happens on workbook open
If cn Is Nothing Then
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & WCHSBook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
End With
End If
End Sub

View Replies!   View Related
Write Values From ADO Connection
i have a ADO Connection. My query is like this:

select [C1] from [values$]
It works, but my problem is to write this values.
Cause i work with my workbook open (not that from connection) and i have something like 55,000 rows to change value. A complex range:

I have a loop for each cell in this range, but this process is very slow.


For Each rng In rng
rng.Value = objRecordset(0)
Next rng
55,000? So slow...

I saw something about QueryTable,

View Replies!   View Related
ActiveSheet.QueryTables.Add Connection:
Sub WebQuery()
Dim strSearch As String
strSearch = "abc"

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & strSearch & "", _
Destination:= Range("A1"))
.Name = "search?hl=en&ie=UTF-8&oe=UTF-8&q=" & strSearch & ""
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

End Sub...

View Replies!   View Related
Mysql Database Connection
I'm trying to run som e query using VBA. How can I open a database connection to mySQL?

View Replies!   View Related
VBA Coding To Detect Internet Connection
Afternoon All - i have an excel workbook that runs on 20 remote machines that i do not have direct access to - i am placing upgrades on the net but looking at finding a way for excel to detect the update what i need it to do first is check if the machine is connected to the internet.

View Replies!   View Related
Stop Internet Connection In VB Works On One PC But Not On
I have the following code in Excel which stops the connection to the internet

'code below

Option Explicit
Private Declare Function InternetCheckConnection Lib "wininet.dll" Alias "InternetCheckConnectionA" (ByVal lpszUrl As String, ByVal dwFlags As Long, ByVal dwReserved As Long) As Long

Private Declare Function InternetAutodial Lib "wininet.dll" (ByVal dwFlags As Long, ByVal dwReserved As Long) As Long
Private Declare Function InternetAutodialHangup Lib "wininet.dll" (ByVal dwReserved As Long) As Long

Sub Stop_BroadBand()

If InternetAutodialHangup(0) Then
End If

End Sub

'code end

View Replies!   View Related
Access To Excel Data Connection
I've created a connection which draws information from an access table to excel. The issue is that any numbers (they are ratings from 1-10) that come from access are shown in excel 1 number higher than the correct data that is in access (i.e 10 is shown as 11, 9 as 10, etc). I've looked all over the forum as well as google and can't find any information on how to correct this.

View Replies!   View Related
Web Data Connection Parameter Problem
I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.

View Replies!   View Related
Query Parameters Which Takes The Date From The Cell Into The Query
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.

My query is this


View Replies!   View Related
Conditional Formatting With Sharepoint Data Connection
I have an Excel workbook that has a data connection to a Sharepoint List. I have 4 columns showing the quaterly status of a project. I want to apply conditional formatting based on the value in the column. I have 5 possible choices so I have to use VBA code to accomplish this because of the limit of 3 option in Excel. I am using this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, Range("X:AA"))
If rng Is Nothing Then
Exit Sub
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "1"
cl.Interior.ColorIndex = 4
Case "2"
cl.Interior.ColorIndex = 6
Case "3"
cl.Interior.ColorIndex = 3
Case "4"...............................

View Replies!   View Related
ADO ADODB.Connection: Preserve Cell Formatting
With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.

View Replies!   View Related
Create An Conection With MS QUERY To A Csv And Query Data
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!

View Replies!   View Related
ADODB Connection: Running SQL Queries On Data Within The Same Workbook
I am simply trying to have some code that would allow me to run relatively simple SQL Queries on Excel Data. This data however will be in a table format within Excel and I will only be needing to query on table at a time; meaning that I don't need relational database features or SQL Joins at all. At most, the SQL Statements will involve WHERE and SORT statements. So, my method is relatively simple:

1. Select an Excel Range (The table being Queried)
2. Provide an SQL Statement
3. Provide an Output range to print the results

After my recent research, I concluded that ODBC/ADODB was the best route. I am very new to ODBC Connections though. Basically, I just set up an ODBC Connection and DataSource using the Control Panel/Administrative Tools. After that, following some templates of others I constructed the following
Sub getData(SQL As String, outRange As Range)
Dim conn As Variant
Dim rs As Variant
Dim cs As String
Dim outCell As Range
Set outCell = outRange.Range("a1")
Dim row As Integer
Dim col As Integer

Set conn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")

cs = "DRIVER=Microsoft Excel Driver (*.xls);UID = admin;UserCommitSync = Yes;Threads = 3;SafeTransactions = 0;ReadOnly = 1;PageTimeout = 5;MaxScanRows = 8;MaxBufferSize = 2048;FIL=excel 8.0;DriverId = 790;DefaultDir=F:2008Cell PhoneCall Details;DBQ=" & ThisWorkbook.Path & "" & ThisWorkbook.Name...............................

View Replies!   View Related
Formula To Decrease A Margin, In Connection With Increasing Basic Value
I'm trying to build a formula to form a price-list. I have some basic prices from a supplier and want to build my prices with a simple rule: the higher the basic price is (column A), the lower my profit margin (in %) should be (column B). Example:

Basic value is $50, my price is $75 (50% margin)
Basic value is $100, my price is $130 (30% margin)
Basic value is $150, my price is $172,5 (15% margin)
And so on...

I forgot most of what I've learned on Excel at my university (long time ago...), so I tried to do it by using simple thresholds, with "if" function:

View Replies!   View Related
Query An Access Query With VBA Function
I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

View Replies!   View Related
Edit Query Using Query Wizard
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.

My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..

View Replies!   View Related
MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View Replies!   View Related
Replacing Misspelled Sub-string In Varying Full String
I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:

Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2

I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.

Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...

Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".

View Replies!   View Related
Conditional String: Repeat The Same Account Number In The String
I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?

View Replies!   View Related
Extract Alpha From A String And Compare With Another String
I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!

View Replies!   View Related
If Text Found In String Return String
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.

View Replies!   View Related
Finding The Frequency Of A String In Other String(s)
I'm looking for a formula that will count specific word in a cell.

I want to know how many WWLL are in this cell, which is 4.

I wrote this custom function to provide the answer. It is used like so: ..

View Replies!   View Related
Replace String Within A String
How to extract out the (M) from a string and convert (M) in that string to another value? Example: I want to extract out the (M) from a string and convert the (M) to "Medium". The data is: Burger (M) with french fries. How to exract (M) from the data and convert the value (M) to "Medium"?

View Replies!   View Related
Convert A String To A "real" String (vba)
How can any string valid for the name of a workbook be converted to a string correctly recognized by Application.Run? Sometimes a string is not really recognized as a string. See for example: .....

View Replies!   View Related
Web Query
i import a table from the internet through web query and i reformat the cells to suit my liking, but when i refresh the data the cell formatting goes back to how it started. how do i make it so it always keeps that same cell formatting?

View Replies!   View Related
Is it possible to open microsoft query without opening Excel or Query Wizerd.

View Replies!   View Related
Web Query A Pdf?
Is it possible to use the Web Query function in Excel on a PDF file?

View Replies!   View Related
Re-using Query Names
how I would be able to weed out and remove previous references to query names.

For example if I have an external data query named "qry1 by week", then remove the query, then create another one, how do I use the same query name again? Surely there's a reference to it somewhere, that I can eliminate?

View Replies!   View Related
Ranking Query
I have a spreadsheet which we use to rank scores for our golf society.

At the start of our season it was decided that if anyone of the top 3 scores were tied we would separate them using a second criteria(back nine score). Any scores from position 4 down would still be tied.

From searching this forum i have managed to separate ties using the 'back nine' second criteria(using a hidden helper column) but only for all the ranking positions not just the top 3.

I have attached the file.

View Replies!   View Related
Count If Query
Is it possible to make an if statement in a cell that only adds the cells in a collum if they are not struck through.

I use this for when an order is complete and would like my total at the bottom of the column to change when I strike out an order in the list.

View Replies!   View Related
Update SQL Query Rather Than ADD
This is to run stored procedures (in the SQL Database), and pass variables. But...once the query is setup...all I really want to do is change the variable...and "fix" the sql query with the new variable value.

Is there a better way than: ....

View Replies!   View Related
Query Filter
I am "Getting External Data" from our main frame. I have a column in the data named item and only want to bring in "Items" with 5 charactors. There is not a Len filter in query and I have tried to use the "Like" with some wild cards with no success.

View Replies!   View Related
For - Next Loop In Query
I have a sheet with a list of names. Row1 is the header row.
I want to query a database and return the status of each name in column D to Column J. I got my query setup and it works, I just can't quite figure out the For Next Loop:

Sub Status_Query()
Dim wholename As String
wholename = Range("D2").Text

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=MS Access Database;DBQ=C:Program FilesMyDatabasesdatabase1.mde;DefaultDir=C:Program FilesMyDatabasesdatabase;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;Trusted_Connection=YES"), Destination:=Range("J2"))
.CommandText = Array("SELECT Clients.ClientStatus FROM Clients WHERE Clients.Wholename ='" & wholename & "' ")........................

View Replies!   View Related
Copyright © 2005-08, All rights reserved