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:
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.
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).
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.
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 RelatedWhy 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] ...........
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.
I need to connect to an Oracle 10g database using vba. Google has loads of snipets of code but I don't seem to be able to get it to quite work correctly.
This is what I have:
Code:
Sub ADOExcelSQLServer()
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
[code]....
When the connection attempts to open (red code) I get the following error:
Run-time error '-2147467259 (80004005)':
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
My best guess is that I do not have the correct drivers installed... If this is the case which drivers do I need and where can you get them from?
I have a report that I import from an Oracle database.. My problem is "Sometimes" Column "L" is there and sometimes it isn't. It is a blank column that the system uses as some sort of place holder & I need to test if its there, then delete. See example below ...
View 20 Replies View RelatedI am running Excel 2007 and connecting to an Oracle DB.
I have created a VBA macro to connect to an oracle DB and retrieve data then place it in a table. The code works, but it uses DSN entries which are specific to my computer. I want to be able to distribute this Excel spreadsheet to others in my company and have them be able to click one button and update the data. I figure the most logical way is to connect to the DB using an IP address that should work for anyone on the intranet.
How do I modify the connection info below to have it connect via IP?
I tried "Data Source = 10.1.1.10orcl" and "Data Source = 10.1.1.10", both of which VBA dislikes.
Here is what I have now:
Code:
Sub Create()
Dim Servername As Range
Set Servername = ActiveWorkbook.Sheets("Summary").Range("B16")
Set StartDate = ActiveWorkbook.Sheets("Summary").Range("B3")
[Code] ..........
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..
I'm trying to run som e query using VBA. How can I open a database connection to mySQL?
View 2 Replies View RelatedI have a code which sends some keys to oracle and performs some actions. What I want is, I need to check a screen in Oracle and then continue or stop, based on the screen. So, I need a msgbox with a yes or no. Is it possible that a msgbox with yes/no be popped up on the screen or oracle? I understand that it can happen on excel screen.
View 2 Replies View RelatedIt says that she has to install excel when she tries to open a folder from oracle. I tried to associted to excel but it did not work.
View 2 Replies View RelatedI'm generating a report on an oracle based software which then I export to Excel. Unfortunately the dates come out very messy. I've included an .xls file with the dates. They are untouched. As you can see, some are left aligned some right aligned. Nevertheless, all dates are of the format dd/mm/yyyy with zeros (0) automatically omitted - this is how they're shown on the Oracle report, I've changed nothing.
I want all the dates on column B to have the same format as the date on cell D3. Of course, the format must remain dd/mm/yyyy.
Please feel free to ask me questions if any of this is confusing.
I Have a workbook which consists of 50 worksheets and i use Edit query window to pull the data from oracle on all these sheets. We run this once in a month. I have to go to every sheet and execute this code which is time consuming and i have to change the date value everytime. macro that can execute this process at once and we should give the date only once.I use OLEDB driver to connect to oracle.
Attached is the Command line code.
Select protocol, patient, Page, (date_indexed) Indexed,(first_entry_date) FirstPass,( second_entry_date) SecondPass,trim( modified_date) modified, trim(CRF_ERROR_COMMENT) Comments from JJA38377_96_crf where date_indexed>='1-May-11' and date_indexed
I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.
So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be:
[StartDate] = #2002/10/1#
[EndDate] = #2002/11/1#
Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is:
Between [StartDate] + .25 and [EndDate] + .25
Note: The +.25 represents 1/4day = 6hrs (6:00AM)
If I use the hardcoded Start/End Dates, my query works fine, but if I try and
pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....
If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it.
I'm starting to pull my few remaining hairs out!
[ This Message was edited by: Tuner on 2002-11-07 12:23 ]
[ This Message was edited by: Tuner on 2002-11-07 12:28 ]
[ This Message was edited by: Tuner on 2002-11-12 12:51 ]
I've got an Excel document I've built which uses multiple ODBC QueryTables
View 3 Replies View RelatedOne of my engaging tasks at work is to disable/enable general ledger accounts (accounting speak) in our Oracle ERP application using one of their forms.
In this case it would be a list of gl accounts and to the left of the gl account would be a check box that I can click.
Checked means enabled.
Blank would mean disabled.
If I have a list of currently enabled gl accounts that are to be disabled (unchecked) then I have to do so one by one. I can click with the mouse on the checkbox or I can using the keyboard use the space bar followed by the down arrow key and repeat. As fun as that sounds sometimes I'm faced with dozens or hundreds at a time.
Therefore my question is can I automate this using VBA? or any other tools out there.
Using Excel Office 2007, Oracle ERP (9.5.8) I think - it's old
I currently have an Excel file with the ODBC connection and using my own SQL. Doing it this way would require the super user to go into each sheet then to the SQL each time to change the dates. I know there is a way to allow them to simply change the dates in cells H1 (or H1 and H2) then refresh data and whoolaa it would be updated.
My goal if possible via this board and all the knowledge available would be to have a spreadsheet allowing a super user to change the date in Cells H1 and H1 & H2 on sheet Tst1 and have the data returned to sheets (Tst2, Tst3) based on my own SQL statements.
Here are some parameters that I think you might need to know.
(And as you may guess I do not have much VBA experience so if at all possible use my naming convention shown below. and feel free to write for a first grader.)
User Name (not actual): "UID"
Server (not actual): "DEV"
Password (not actual): "PWD"
File Name: Excel_ODBC.xls
Sheet Name: "Tst2"
"Tst3"
Output Cell: "A1" for both sheets.
Oracle table name: UAB .................
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.
I would like an ODBC query to return the reults to a textbox on my userform.
View 1 Replies View RelatedDoes 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 RelatedI 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?
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 RelatedI am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
Database 1
Eric 100
Jenny 200
Gina 300
Doug 400
Database 2
Eric 18
Jenny 20
Gina 34
Doug 55
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.
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).