VBA Set ODBC Connection String For Query

Jul 25, 2008

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

ADVERTISEMENT

Excel 2007 :: ODBC Connection Query Shows No Result

Mar 25, 2013

Why this sql query doesn't return values? My excel version is 2007.

Code:
if object_id( 'TEMPDB..#TMPDOC') is not null
begin
drop table #TMPDOC
end

create table #TMPDOC (Code varchar(5) NOT NULL)

[Code] ...........

View 6 Replies View Related

Excel 2010 :: ODBC Connection String With Multiple Variable (WHERE) Requirements

Dec 29, 2013

With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.

This is a two part process:

Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")

Here is part one

Code:

Sub Part1()
Sheets("Open PO by Vendor").Select
Sheets("Open PO by Vendor").Cells.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _
, Destination:=Range("$A$1")).QueryTable

[code]...

Part one works perfectly.

Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.

I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.

As follows:

In particular: *E3 from Part 1*, etc.

Code:
"SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _
, _
"tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _
)

Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.

1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.

2. Create a nested if table and

Change

Code:
WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"

to the value of that nested if table

The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.

3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.

What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.

View 5 Replies View Related

ODBC Connection To An Oracle Database

Nov 14, 2009

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:

MFE.Items
MFE.Vendors

Sample SQL string would be:

View 2 Replies View Related

Multiple Dropdown List With ODBC Connection

Jul 15, 2014

I'm trying to create a dropdown list on my "job master" sheet with reference to cells in an ODBC connection where a position "description" will give me a list of names. Then I want to be able to select another list of names individually with reference to the name I Just selected.

Staff
Description
Foreman

[Code].....

*I was able to do this using lists and indirect list (Personnel Sheet) but it doesn't work with my ODBC connection. I attached the excel file I am using.

View 1 Replies View Related

Login And Password Prompt Prior To ODBC Connection?

Apr 23, 2013

i currently have an excel based application with VBA code. I'm trying to add some controls around the app by prompting user for login and password credentials prior to ODBC connection at runtime. So when application is launched, prompt appears, user enters login and password, if correct, it goes on to verify user has ODBC connection at runtime.

View 2 Replies View Related

ODBC Query Returns To Textbox?

Oct 20, 2011

I would like an ODBC query to return the reults to a textbox on my userform.

View 1 Replies View Related

Excel 2010 :: Assigning Name To Range Received By ODBC Query

Apr 29, 2014

I have been working on some ODBC queries in Excel 2010.

First, I recorded them using Excel's Record Macro function, to get an idea of what sort of source data I would need. Then, I rewrote them into something a little more intelligible.

Pulling out all the sensitive info, I want to know how to add the .ListObject.DisplayName property back onto the results of the query. I tried doing it intuitively, but it didn't work. It was in there when I recorded the macro, but I can't seem to figure out where to put it back into the re-written code. Other parts of the code depend on the results of the query being a 'named field'.

[Code] .....

Also, where would I find out what all that stuff in Cnnect means? DBA, APA, EXC, FEN, etc are all just assignments, and I might like to change some to make this run a bit quicker, if I knew what they meant.

View 6 Replies View Related

Assigning Cell Value In Web Query Connection?

Jun 15, 2014

I have a macro which retrieves data from web.It works fine.I just don't know how to assign a cell value in web address.i have attached the code here. i just want to assign activesheet.(range"c2").value for "q?s".

View 1 Replies View Related

Running Access Query In Excel And Connection

Aug 5, 2003

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 9 Replies View Related

Working Access Query Fails When Using Excel Data Connection

Sep 6, 2012

I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.

I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.

SQL code below:

Code:
SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;

View 2 Replies View Related

Excel 2007 :: Workbook Use One Data Connection With Different SQL Query For Each Worksheet?

Oct 13, 2013

I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.

I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.

View 2 Replies View Related

Pivot Refresh After ODBC Query Complete Refresh

Jan 4, 2008

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 9 Replies View Related

Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden

Oct 19, 2012

I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.

btw...I am running Office 2010

View 2 Replies View Related

Data Connection From Web / Web Query Returns Code Instead Of Selected Data Table

May 31, 2014

A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.

The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.

I can't talk to the people who changed the web program.

View 3 Replies View Related

Find Text String In Web Query

Feb 18, 2010

I want to find the number within this webpage and report it to a Cell in excel....

View 6 Replies View Related

Numeric String Manipulation Query

Dec 23, 2009

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 9 Replies View Related

Query Parameters: Editing The Middle Of A String

Jan 3, 2007

My question is in regards to query and setting up parameters. I have the following query (I am using an example for security purposes)

http://examplenet.test.com/aaaa/Search/SearchList.aspx?type=Number&criteria=AA21&mode=New&rfilter=****&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:

http://examplenet.test.com/aaaa/Search/SearchList.aspx?type=Number&criteria=AA33&mode=New&rfilter=****&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 6 Replies View Related

Mixing ADO And ODBC

Dec 30, 2009

I've got an Excel document I've built which uses multiple ODBC QueryTables

View 3 Replies View Related

Connect To Access Using ODBC

Apr 17, 2009

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 9 Replies View Related

API Call To Get List Of ODBC DSNs

Mar 25, 2002

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 9 Replies View Related

Index/match/vlookup And ODBC SQL

May 7, 2009

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 9 Replies View Related

ODBC Lookup To Populate Variable

Apr 14, 2006

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 3 Replies View Related

Connecting To A Database Without The Local Machines Odbc

May 4, 2007

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 9 Replies View Related

ODBC :: Importing External Data From An Oracle 9i Database

Mar 18, 2008

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 9 Replies View Related

Open / Refresh Connections To ODBC - Recalculate One Sheet / Save And Close

Mar 25, 2014

I am using Task scheduler to open a workbook which has some connections to an ODBC that are set to refresh on opening.

I need to automate:-

One of the sheets needs to recalculate for the sort by ascending code to run on the sheet.

Save and then close after say 2 minutes of opening (to give the ODBC connections enough time to finish).

View 2 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

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
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

View 2 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

Sep 6, 2008

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 9 Replies View Related

DBGrid Connection Through ADO (SQL)

Jul 18, 2009

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 9 Replies View Related

Workbook Connection

Jun 8, 2006

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 9 Replies View Related

SAP Connection - Which References

Sep 7, 2006

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 5 Replies View Related

Query An Access Query With VBA Function

Jul 8, 2008

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 9 Replies View Related

Edit Query Using Query Wizard

Apr 25, 2006

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 2 Replies View Related

How To Remove Data Connection

Aug 4, 2014

I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.

When I click data --> connections its empty.

View 2 Replies View Related

DB Connection Lockout Other DB Connections

Mar 7, 2007

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 9 Replies View Related

VPN Connection Causes Macro Error

Jul 11, 2014

This file connects to a weather API. The laptop I am using connects through a VPN connection. For some reason whenever I am on the VPN if I try to refresh the information I will get an error and the macro will fault out. Other than that the macro works perfectly fine.

Attached is the file in question : Weather Forecast Spreadsheet Draft V12.xlsm‎

When debugging the error goes to:

How can I place in an error management instead of the code just getting blocked out.

View 1 Replies View Related

Can't Remove Public ADO Connection

Feb 12, 2009

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;"""
.Open
End With
End If
End Sub

View 9 Replies View Related

Write Values From ADO Connection

Jul 12, 2009

i have a ADO Connection. My query is like this:

select [C1] from [values$]
It works, but my problem is to write this values.
Why?
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:

D2,D11:D12,D14:D20,D32,D42,D52,D62,D72,D82,D92,D101:D102,D104:D112...
I have a loop for each cell in this range, but this process is very slow.

Imagine:

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

I saw something about QueryTable,

View 9 Replies View Related

ActiveSheet.QueryTables.Add Connection:

Jun 13, 2006

Sub WebQuery()
Dim strSearch As String
strSearch = "abc"

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.xyz.com/&=" & 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 6 Replies View Related

Mysql Database Connection

Sep 21, 2006

I'm trying to run som e query using VBA. How can I open a database connection to mySQL?

View 2 Replies View Related

ADVERTISEMENT