Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


Free Excel Forum

Odbc Connection

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have set up ODBC connections to my worksheet using the MS Excel driver.

That works. I am able to read my spreadsheet using SQL.

However, am I meant to be able to use Excel functions? What I'd really like is to determine the row as I am reading the source but I don't know the best approach.

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Display or Show all of the Comments within an Entire Excel Workbook
- This macro in Excel will display all of the comments within the entire Excel workbook. This means that all of the comme
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics







I am having trouble defining an OLEDB connection string to
an Oracle database. I have no problem using an ODBC
connection.

Works (ODBC)
obj.Connection = "ODBC;DRIVER={Microsoft ODBC for
Oracle};SERVER=IFAS;UID=scott;PWD=tiger"

Doesn't work (OLEDB)
obj.Connection = "OLEDB; Provider=MSDAORA; Data
Source=ifas; User ID=scott; Password=tiger;"

Does a DSN need to be created for OLEDB connections? What
am I missing? TIA...

John



I have a sybase query that I have changed the connection info on from this:

ODBC;DRIVER={Sybase ASE ODBC Driver};UID=AR2_RO;SRVR=SYBASE;DB=AR2;

to this:

ODBC;DRIVER={Sybase ASE ODBC Driver};UID=AR7RO;SRVR=GRITRU;DB=AR7;

The databases are identical but are for two different companies. I changed the connection using the following command, which took the change fine (I confirmed the connection was indeed changed):
activecell.querytable.connection = "ODBC;DRIVER={Sybase ASE ODBC Driver};UID=AR7RO;SRVR=GRITRU;DB=AR7;"

The problem is that when I attempt to refresh the query I get the error "User ID 926 is not a valid user on AR2" where AR2 was the orginal connection DB.

Do I need to do more than just change the connection, if so any help is appreciated.


Hi everyone,

I've made a database that has a couple linked tables connected to our SQL servers. I had to create an ODBC connection on my PC in order to access them. When I sent a copy of the database to my co-worker to check out he can't access the ODBC because he doesn't have the same connections set up on his PC like I do.

I've never really dealt with ODBC connections before so I'm a total beginner when it comes to this. I'm assuming there's a way to have other users access a database on their own PCs without having to set up identical connections on each PC.

So what can you all tell me to help me out?

Thanks!
Shane


I have an Excel 2000 sheet based on a MS Query to a SQL Server 2000 database.
When I refresh the query, the ODBC connection fails with this error:

SQLState: '01000'
SQL Server Error: 11001
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets][ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 6
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL Server not
found.

An Access database with links to this SQL Server data source works just
fine. I tested the ODBC connection and it works fine.

The problem cropped up after the database and Excel sheets were moved to a
new physical server.

I tried to edit the Query in hopes of recreating it from scratch but I can't
see the Query because the ODBC error appears and won't open the query editor.
How can I fix the error or open the query so I can manually recreate it?

Thanks!

--Art



I am trying to read an Excel file using the 'Microsoft Excel Driver (*.xls)' ODBC driver. It works fine if the file is on my local computer or on a mapped network drive. However, the file I need to open is on an intranet location, so all I have is the url for the file. Is there any way to read this data using ODBC?

I am able to open the file using the 'workbooks.open' function with the url, however I am trying to convert to the ODBC as it should be significantly faster to run.

Any help would be greatly appreciated!


I have set up an ODBC connection via the Admin tools under control panel and it connects fine.

However, when I try to use the same connection to update from Excel, I receive the error ORA-12154: TNS: could not resolve the connect identifier specified.

I have validated that the tnsnames.ora file is in my current path.

I'm using the Oracle in instantclient10_2 driver to create the ODBC Data Source.

Any help would be SOOOO appreciated!

Thanks!!
Andrea


Is there a way to see the SQL query that someone built in the query editor without actually having the ODBC connection installed on your machine? I assume the query syntax resides in the spreadsheet somewhere, not on the database it is querying.

I keep getting the error "[Microsoft][ODBC Driver Manager] Data Source name not found and no default driver specified"


Hi

I wonder if it is possible to make a read-only connection to an Excel 2010 document, with the Excel document being an ODBC data source.

It is not possible for Excel 2007 documents. If connecting to an open Excel 2007 document on a server, the following error message is given:

"It is already opened exclusively by another user, or you need permission to view its data" (when trying to connect using JDBC-ODBC bridge in Java)

Is it possible to make a read-only ODBC connection to Excel 2010?


Question 1: I would like to distribute a sample of my workbook, with the ODBC connection hardwired to go to a specific directory on their C drive. I don't want the user to be able to use the Script Editor to change the ODBC connection string. (Or any other program.) Is there a way to lock this?

Question 2 (but related so I'm listing it here): Right now we set the ODBC connection when we create the workbook. We click on the worksheet, choose Import External Data and choose the Visual FoxPro ODBC driver and connect to our sample company. When I get a new user, I use MS Query to go in and edit the connection string (9 times...) -- is therees a better way to do this? Can I do it in the VBA code directly? Assuming I could I'm guessing this would solve my Question 1.

Thank you. I did see some posts regarding connection strings, but they were a bit above my head. Please speak slowly...


I have read through alot of posts about excel and ODBC and none that I have read seem to pertain to my situation. I am using a program called Elipse SCADA which has a built in database system using the ODBC protocol. There is very little documentaion on the Elipse software but I have named a table in my excel file and Elipse is able to see it. The only connection parameter that i can edit from the Elipse side is something called the ODBC connection string. which is currenty set to:

ODBC;DSN=Arquivos do Excel;DBQ=d:\ABM\Forno\Test.xls;DefaultDir=d:\ABM\Forno;DriverId=790;MaxBufferSize=2048;PageTimeout=50;

I am able to sucessfully connect to the excel 2003 file but when i try to write an entry to it i get and error message saying that the 'sheet is full'. I am very new to ODBC and Im wondering if there is some seting I need to enable in my excel file so that the ODBC server can write to it. Any help would be apreciated including links to some introductory documentation about using ODBC and excel.

Thanks
Adrian


Hi All,

I hope somewon can help.

I have a workbook (workbook1) with 1 sheet (data source) which has around 1200 lines of data and 111 columns. Every cell is filled with data and there are unique headings accross the top.

I then have another workbook (Workbook2) which has about 50 sheets in it. Each sheet has a pivot table linked to Workbook1 via an ODBC connection.

ODBC is setup under USER DSN and is 'Driver do Microsoft Excel(*.xls)' linked to workbook1.

Problem
-------

Last year I created about 50 pivot tables in Workbook 2 with charts attached to each one. I was refreshing them every day between Jan and Feb 2006. I came to re-use them the other day and when I hit refresh I had the message:

[Microsoft][ODBC Excel Driver] The Microsoft Jet database engine could not filnd the object "Data Source$". Make sure the object exists and that you spell the name and path name correctly."

The database name (spreadsheet name), location, or sheet name hasn't changed. Nor has the ODBC link name changed. I have checked! :-(

I have tried re-pointing the ODBC link to the spreadsheet, and re-creating the ODBC link and nothing.

If I go into the Wizard and press back and click on get data I get the error:

'The query cannot be edited by the Query Wizard'


Any ideas. Any help would be very much apreciated.

Tim


I have a .db file that has data I need to export into excel. Everytime I try to make a connection between the two, I get this message:

Unexpected error from external database driver (8961)

I tried the Microsoft solution by reinstalling BDE but it didn't change anything. Some info:

Windows XP
Microsoft Excel 2007
Microsoft Jet OLE DB 4.0 Provider 4.0.9502.0
Microsoft VFP OLE DB Provider N/A
Microsoft VFP ODBC Driver 1.0.2.0
Microsoft dBase ODBC Driver 4.0.6305.0
MS SQL Server OLE DB Provider 2000.85.1132.0
MS SQL Server ODBC Driver 2000.85.1132.0
Firebird/InterBase(r) ODBC Driver N/A
Borland dbExpress Interbase Driver N/A
BDE InterBase SQL Links Driver 5.1.0.4
BDE (Borland Database Engine) 5.1.0.4
Microsoft Data Access Components 2.81.1132.0
Microsoft Data Objects Extensions 2.81.1132.0
OLE DB Provider for ODBC Drivers 2.81.1132.0


I am having trouble with using certain mysql functions via the odbc connection in excel.

Query:

select name, monthname(Dob), sum(wages) from userTable group by name, month(Dob);

This query works in Mysql query browser.
But in excel when I use the odbc data connection, I loose the whole monthname(Dob) column. I tried using date_format(), but no luck

Can someone help me?


Hi

Is there a way to check which odbc an excel sheet is connecting to? I have a number of datasource connections and I'm not sure how I would find the specific connection for a sheet.

I would appreciate any pointers.


Hello,

I have an Excel 2003 macro that updates a querytable during one step. In this case, the data source is an ODBC entry via Microsoft Query. This is a macro that I need to distribute to others in my company. These users might, or might not have the ODBC connection correctly configured. If the macro is run on a computer that has the ODBC entry correctly configured, the macro runs perfectly fine.

However, if theODBC entry is not correctly configured, a Windows XP 'Select Data Source' dialog box appears, prompting for the correct data source. This makes sense, but it can be confusing for some users.

Is there any way that I can have Excel throw up a MsgBox instead of the 'Select Data Source' in these cases? I would use this MsgBox to relate instructions on how the user could contact IT and get the ODBC connection/permissions set up.

Thanks in advance for your help!


I have an Access mdb connecting to a SQL database over ODBC. The application has numerous forms and queries that run as users enter and or edit data. For example, a user will edit a record and click Save. At this time the screen will requery. I have noticed during a typical day the network connection will go through peaks and valleys. My user's tend to get the ODBC error when the network connection is very low.

Is there a way to test the connection before any reading or writing is done? In turn avoid the ODBC errors?


Hi

I have an Excel spreadsheet linked through to a sql table via and ODBC connection and I have a macro to refresh the data. I want the macro to first check that the ODBC connection exists before trying to refresh the data. So it would look like:

IF ODBC Connection exists THEN refresh data ELSE return warning message.

becoz when connection cannot be made, its gives me error. and i have to manually press ok button.


Any ideas....?

thanks


Currently I have an excel 2007 spreadheeet with around 400 columns of data, I was then hoping to make an ODBC connection to this from a worksheet on a second spreadsheet. Unfortunately only 250 odd columns are actually being recognised using this ODBC link, is there a way around this?


Hi. Can someone tell me how to use VBA to set the ODBC connection string for queries?

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

Thanks!


Note, I screwed up my original question and I apologize for cross-posting. My original question was posted under the title: Connection Strings in ODBC - lockable? -- note I would still like to know if they are lockable/protectable, but that was a secondary issue... Sorry!


I have a work book that uses ODBC drivers, I wish for this workbook to be viewed by multiple users as the data is pulled from one source off a SQL server.

I know how to install the ODBC driver info on my machine and run a query, but I have noticed that the driver info etc... is stalled only for me to use. The moment I attempt another user to view the work book it fails.

Any IdeaS?


Hi,
Is it possible to create a pivot table using VBA where the source is an ODBC source? In my case it's a tab delimited file.
Attached is my code. Instead of reading the file, an ODBC data source selection dialog box appears instead when ActiveSheet.PivotTableWizard is executed (lv_src has a valid value):

VB:

connectString = "ODBC; Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=" & lv_src & "; Extensions=asc,csv,tab,txt; UID=;;" 
QArray = Array(ConnectString, "SELECT * FROM InvSales.txt") 
ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:= _ 
QArray, TableDestination:=Worksheets(lv_pivot).Cells(1, 1), TableName:="PivotTable1" 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Any help very much appreciated!

Hi All,

I have some code that people here helped me with earlier in this thread:

http://www.excelforum.com/excel-prog...ransposed.html

Some new problems have popped up that I was hoping someone could help me with.

I tried to transfer the workbook to a coworker and he couldn't get the connection to oracle to work. So I was playing around with different connect strings with the file on my machine hoping to find another one that works with our setups so I could try it on his machine.

When I first set this code up I was using

Driver={Microsoft ODBC Driver for Oracle}

and everything was working great. In my experimentation I changed the driver to this:

Driver={Oracle in OraClient10g_home1}

and discovered some new problems:

The connection still works, but now when my macro executes this step:

ws.Range("A2").CopyFromRecordset sqlresults

returns only the first row of the resulting query results. Whereas before I changed the connect string, it would put the entire query, multiple rows, into the range starting at A2.

It seems very odd that the behavior of that would change depending on the connect string used to connect to oracle, but it did because I didn't touch anything else in the code.

It gets worse, I didn't like this so I changed the connect string back to using Driver={Microsoft ODBC Driver for Oracle} and now it won't connect at all. I get an error message saying

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

What the heck? It's like something in my machine outside of excel changed when I tried that other connect string and it broke whatever was making the original connect string work.

So now I'm stuck with either a macro that doesn't function because it can't connect to oracle, or a macro that doesn't function because it returns only the first row of a query instead of all of them.

Can anyone help?

Thanks,

Dave


I have a client that has hundreds of spreadsheets that use database queries created within excel (not vb) to link to data stored in a third-party database (Pervasive) via ODBC.

Recently that datbase moved from Drive D: to Drive E:. I updated the ODBC connection to reflect the move. Unfortunately, now none of the excel queries work. I get an ODBC connection error.

If I create a brand new query using the same DSN, it works fine. The DSN also works fine in Access and Word.

I guess when the query is stored in Excel, it stores the the data location within the spreadsheet and no longer references the DSN. Is there any way to view or modify this connection short of creating new queries in hundreds of spreadsheets.

Thanks


Hi,

Two questions (background below):
1. Why did Excel suddenly start prompting me to specify a ODBC data source?
2. How can I edit old *.dqy queries in MS Query now that it gives the error "Data source name not found and no default driver specified"?

Explaination:

I have a workbook that contains several macros (recorded many years ago, not written from scratch) that call on some *.dqy query files (also created many years ago, using MS Query) to query data located one worksheet and output the results into different worksheets. I currently use Excel 2003 on Windows XP.

About a year ago, I was working on a different project using SQL and databases using OpenOffice Base. After the first time I used that software, Excel suddenly started asking me to specify an ODBC data source when running my old macros in this old workbook. I was puzzled, but fixed it by making a new data source out of the workbook. Why did this start happening? Can I make it stop?

Now these macros have stopped running again (getting stuck at .Refresh BackgroundQuery:=False). In an effort to debug, or perhaps just rewrite the macros properly, I tried to check the *.dqy query files in MS Query. However, MS Query does not seem to like that these files were created before my computer suddenly started wanting me to specify ODBC data sources, and gives the error "Data source name not found and no default driver specified" followed by "Couldn't read file".

Any insights welcome. This ODBC stuff is all new to me!

Thanks!
-Amanda


My colleague pulled data into Excel from an Access database on his desktop using an ODBC (user DSN with password). He recently left the company and the database was moved to a share drive. When I refreshed the data on Excel, I got an ODBC Fail error message. There are altogether 39 queries. How can I update the ODBC connections on that Excel file?

Thanks,

PKL