Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Using ODBC In VBA (Excel)

I have a MySQL database and a corresponding ODBC data source.
I have added the ODBC add-in (XLODBC.XLA) to Excel (2000) application.
Now, I want to connect to database and perform a simple query.

How to start? What type objects must I declare?
A simple short example code should be wonderful.


View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Talking To Excel Via ODBC

I am making a small application to send emails to multiple recipients. For now the program should import recipients from an Excel spreadsheet, but in the future multiple formats could be supported. I have already done the import by talking to an instance of an Excel spreadsheet (dim app as Excel.Application, etc)
However, if multiple formats of data are to be supported in the future, I should ideally use ODBC to connect to the workbook. This way I could talk to a DBF or flatfile in the same fashion without changing too much code. I am able to make a connection to an Excel workbook via DataEnvironment (via UDLs), but I do not know how to access the data via ADO. Is it possible? If yes, I would appreciate even the tiniest tip.

Thanks a lot in advance,

I Want Ot Import From Excel Using ODBC. How
I know I need to go out to control panel and create my DSN file which I have done. Now how do I write code to open the connection and read first line?

I am new to this so thanks in advance!

ODBC Dialog Box In Excel
Does anyone know how to display and control the standard dialog box that shows the list of existing ODBC connections?



Excel RDO And ODBC Connections
With all the hassle of using excel as an object within other applications. I was wondering about about the other ways to pull data out of Excel. I've been told that you can use RDO or even ODBC to create a recordset from Excel. How does this work? What are the advantages and disadvantages?


ODBC Excel Driver
I am using the following code to open a connection to an Excel workbook but whenever I run the code it opens the workbook that I am querying?!

Set oConn = New ADODB.Connection
oConn.Open _
"Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & sFile & ";" & _
"DefaultDir=c: emp"

Set oRs = New ADODB.Recordset

strSQL = "Select * FROM Wherever$;"

oRs.Open strSQL, oConn, adOpenStatic, , adCmdText

What I want to do is query the Excel workbook without it opening. How do I achieve this?

ODBC Import To Excel Via VBA Code
I want to get data from another computer through an ODBC connection in VBA code into an Excel spreadsheet. Does anyone know the Provider to use for an ODBC connection in the following code, and do I need an ODBC reference library to use it?

ALSO: I am able to import the data manually through the Excel toolbar now by doing: Data Import External Data Import Data supplying connection name signing onto a connection of format nnn.nnn.nnn.nnn.nnn and specifying where to put the data in my worksheet.

Am I on the right track below to get at this kind of a ODBC connection? Any examples of the Data Source line to use out there?

Dim adoBTC As ADODB.Connection
Dim BTRec As ADODB.Recordset
Set adoBTC = New ADODB.Connection

*** Need this line ***
adoBTC.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:AccessMyDB.mdb""
adoBTC.Open ' open the connection
strTableName = "ODBC Table"
End If
Set BTRec = New ADODB.Recordset
BTRec.Open strTableName, adoBTC, adOpenKeyset,
adLockOptimistic, adCmdTable
code to sequentially read & filter the records into an excel worksheet.


How Do I Open The ODBC Administrator W/ Excel VBA ?
I used to use XLODBC's SQLOpen to cause the ODBC Adminstrator window to open. Everything I read tells me to use ADO.

I need VBA code that opens the ODBC Adminstrator so I can build have it automatically build the Connection String.

ODBC Update Of MSQuery In Excel
I have finally got the first phase of my Excel application running great to retrieve data from Quickbooks in an MSQuery using the QODBC driver (which makes a Quickbook file look like a standard ODBC database). Now I am ready to go to the next phase and start updating Quickbooks from my application. I can't seem to find any relevant examples of updating an ODBC database from Excel. Most of the examples I have examined seem to assume that you have a recordset from an ADO/OLEDB connection. However, when I examine the attributes of my Worksheet.QueryTable, it says "Not a DAO Recordset datasource" for the Recordset property.

For now, at least, I just want to update fields in one record at a time upon user request (i.e., a button click or menu command), especially since I have to select a subset of the fields which are updatable. The Quickbooks SDK interface upon which QODBC is built does not support update of many critical fields that can be retrieved, in order to maintain integrity of its database. What is the best way to go about this. It has been many years since I did any database updating, and at that time it was just simple straightforward SQL in a DB2 database. Any help to get me started in the right direction would be greatly appreciated.

Getting Data Form Excel Using ODBC
Hai guyz,
I am using excel object from vb to get the excel data. but this is working very slow. now i like to retrive / set data from the excel files using odbc connection. pls help me with the examples.

Accessing MS Excel Data Using ODBC
I am trying to open an Excel file using ODBC. So far I have achieved that. However, I wish to read the file like it is table within a Database. I am using ADO access method. How can I retrieve this information into a Recordset?


VBA Excel Updating Using Oracle With ODBC
Hey guys, is it possible to update records in Oracle9i db using MS-Excel? We know that it is possible by using oo4o, but we can't use that.

Can we use ODBC to update the records? If yes, can we have samples or help from you... We are pretty desperate

Vbs, Odbc, Excel, Spreadsheet Is Full
im trying to add records to an excel file using a vbscript. I'm connecting to the excel file through an ADODB connection.

I use this code to connect

PHP Code:


Reading An Excel Worksheet Using ODBC From VB.NET
How do I access an Excel Workbook from VB.NET using the ODBC driver? I want
to read Excel data into a DataGrid control on a web-page. Although ODBC
says it can connect to Excel, it then asks me to design the query in SQL,
and tells me no matter what I specify, that it's wrong.

How do you specify data from Excel via ODBC?
And is it possible to OLE DB as well?



ADO/OLEDB With Excel ODBC Driver - Help!
I am attempting to read the contents of an Excel worksheet into a VB6 program using ADO, OLEDB and the Excel ODBC driver.

The Excel driver has 'intelligence' built in, so it determines the best data type to represent each column in the worksheet. This does not work as I need it to!

I know there are parameters to control the DAO/JET version of the driver, but I can't find any for the ODBC driver.

The connection string I'm using is this:

ConnString = "Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls)};DBQ=" & ExcelPath & ";MaxScanRows=0;"

I use MaxScanRows=0 to try and get the driver to assess the whole worksheet. However, when processing the worksheet I still get NULL values in certain fields, when I am expecting numbers or text.

Are there any parameters I can pass which control how the ODB driver processes the worksheet, in terms of scanning rows, column data types, default conversion, etc?

Scanning MSDN has not helped - as usual the examples are too simplistic, or do not cover what I need.

Any help would be appreciated.

Cheers - Jiminy

Excel ODBC Driver Missing Data Out
I am using an odbc for excel driver to read the contents of an excel file trhough vb into a sql database but some of the data in one column is not all coming through. The column at fault holds the telex number, in some rows this data is right justified (text) or left justified (number) and only the right justified rows are showing any data, the left ones show the column for that row as being empty.

The simplest change I know would be to get the source spreadsheet altered so that the column was all formated into text but I am not sure this is possible, any suggestions on why this is failing. We are using mdac 2.6


Problem Writing Excel Formulas Via ODBC
I have a C++ program writing records to an Excel spreadsheet via ODBC/SQL. The text and number values are inserted ok. I am also trying to write a formula (eg. =HYPERLINK("", "Click here for the abc site") ) into the sheet.

The problem: the formulas are displayed as formulas - they do not calculate. If I edit the formula (changing nothing but simply pressing return to re-enter the original value) then the formula is calculated. In the example above, the hyperlink is displayed as a link.

The same problem exists for any formula (even something as simple as =2+2).

Does anyone know why the formulas are not calculated ? Pressing F9, Shift-F9, Crtl-Alt-F9, or calculating the worksheet with VBA don't have any effect.

I can't believe that formulas have to be manually entered or edited before they become active. If I am missing something please let me know. Thanks in advance.

Urgent - Open An Excel Table To Read Using ODBC
I need to read an Excel file and insert those records into a MS Access database. The range of the data is not fixed. I need to use ODBC. How do I do it?

Problem Writing Data To Excel Files Using ODBC
I derived a class from recordset and made a connection with an already created Excel file to enter and retrive data from it. I am not able to add data to excel files(w/o using SQL). Using ODBC and MFC programming I want to write in excel files. Is it possible to do so? If possible, please provide some sample code to show this.

Retrieving Data From Excel Workbook Using Odbc Driver
I want to retrieve data from excel workbook with odbc driver .
how the select statement should be coded for retrieving data from excel.

Visual Studio Installer - How Do I Package An ODBC Driver And A ODBC Connection?
Can anyone tell me how I can Package an ODBC Driver and a ODBC connection using Visual Studio Installer.

thank in advance

ODBC Error : [Microsoft][ODBC Driver Manager] Connection Not Open
Hi friends,
I am getting error the following error.
ODBC error : [Microsoft][ODBC driver manager] connection not open.

when I am trying to generate my report in crystal report 8.5. Pls help me out.

Thanks in advance.

ODBC Error: [Microsoft][ODBC Driver Manager]Data Source Name Not Found And No Default
I have a VB App accessing Access Database. I have created an installer using Windows Installer. When the application is installed on a PC with Microsoft Office, everything works fine, However, on PC@s with no Office, the following error message pops up

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

The Operating System is XP wit SP2. MDAC 2.8 is installed.

Any help will be highly appreciated.

Help ... ODBC Error ... ODBC Cursor Library Not Capable...

I am saving images into oracle database using rdoResultset and append chunk methods.
While updating the resultset i am getting the following error,

Run-time error : 40002
S1C00 : [Microsoft] [ODBC Cursor Library]
Cursor Library not capable.

i coundn't solve the problem ... please any one had some ideas pass on to me.

Problems Converting From MS ODBC To Oracle ODBC
Im on Oracle client, and Im trying to update some apps to be compatible with Oracle 9i.
I was told Microsoft ODBC for Oracle was not supported on 9i.

So, Im trying to convert over to the Oracle ODBC, but now I am getting errors on all my PLSQL calls, and I dont understand why.

Some of the error messages I am getting:

When I run the following:
Set adoParam = adoCommand.CreateParameter("ERRDESC", adVarChar, adParamOutput, 200)
'Set some other params
adoCommand.Execute nRecordsEffected, , adExecuteNoRecords
I get this error:
[Oracle][ODBC]Restricted data type attribute violation.

This worked before, when using the Microsoft ODBC. What is different?

Another quick Q:
I also tried using the Oracle OLEDB. This connected fine when using ADO, but when I tried to connect with RDO it failed. Is RDO compatible with the Oracle OLEDB?


Advantage Of Microsoft ODBC Driver Over Oracle ODBC Driver
can nay one tell me the Advantage of Microsoft ODBC Driver over Oracle ODBC Driver to connect backend Oracle database

ODBC Error:[Microsoft][ODBC SQL Server][SQL Server]Server User Not A Valid User
I have been facing a problem with Crystal Sub Reports.
In my Report I am using SQL Server Data base. The report contains main report and a sub report.
I am connecting to the database 'masstest'. The report is working fine on 'masstest' Database . If I change to connect
'masstetdump' database, the report is not executed showing an error , ODBC error:[Microsoft][ODBC SQL Server][SQL Server]Server user 'masstetdump' is not a valid user in database 'masstest' .
If I remove the Subreport then the report is working well on any database. The problem is only with sub report.

any body please help me.

This is not really my thing but can anyone explain to me step by step how to make a ODBC connection, VB 6 Front end access 2000 database.

How would I go about something like a login system to a hosted MySQL that I own? I've gotten it to connect, but in my code I am trying to figure out after running this query (SELECT * FROM db.mytable WHERE username='test' AND password = md5('test2'), but I want to fetch the amount rows from that query, and I am used to PHP's mysql_num_rows function. So, any help would be greatly appreciated.

When I create a new System Data Source Name for SQL server it will go through complete process of creating the DSN but when I go to see the newly created DSN, it is not in the list. I know it is there because if I want to re-create it with the same DSN name, I get the message “the DSN exist do you want to over write it”?

Creating a DSN for Microsoft Access, it does not even go pass asking the driver type?

I have re-installed win 2000 SP2, and re-installed MDAC 2.7 still could not solve the proble.

If you have seen this problem and you know the solution, please inform me. It will be appreciated.

Thank you for your time and concerns.

Odbc && Ado
IS it possible to use ODBC using the ADO controll?

I have a *.dbf file that I created in Excel. How do I connect to that file? Do I have to use ODBC and connect to it as a table and use an RSet?

Ado, Dao && Odbc
Can someone give me a breif description of, and the difference between, or point me in the direction where i can find out about:


I seem to have no idea.....

Regards Darren.

I have a Crystal Report 4.5 problem.....

I have a basic ODBC report i have created but can not get it to print/export/preview etc from the VB enviroment.....

It works Ok from the Crystal Reports Enviroment.....

I am calling the control like this :

CReport.ReportFileName = App.Path & "game.rpt"
CReport.Destination = crptToWindow
CReport.SQLQuery = "SELECT * FROM GameData.`GameData` WHERE GameData.`Round` = " & cmbRound.Text
CReport.DiscardSavedData = True

Round = type "String"

I have set the ServerType, ServerName etc during Design Time in Crystal Reports.....

I am wondering if i need any other statements calling the control?
Do i need to Log on to the Server in VB even though i have done it in Crystal Reports?

Am i going about this all wrong?

I have attatched the Report if needed.....

Believe me i have gone through all the help system, i must be missing something...

Regards Darren.

This may be a dumb quetion but...
what is the difference between an ADO connection and ODBC connection....I thought the ODBC just automatically did all the stuff for u(setting up the connection with the simplicity of clicks) whereas ADO u have to specify a driver, server, DB, etc...dont they use all the same driver files???

Dear peepz,

i'm making a program with ado and odbc. Because I need to make a program who can use access, foxpro and sql and mysql.

I want to make a odbc dsn in Visual Basic, but I don't know how to register a db in my system datasources.

Maybe you can help me with this problem.

Allready thnx

hi :
i am sorry..this is going to be a trivial post to most of you...
i want to set up the ODBC data source..but cannot find the ODBC icon in COntrol Panel...can someone explain how to set up an ODBC on your local machine??



I am kind of being challenged by another developer at work regarding an issue to connect SQL Server in a VB6 application. So I just want to make sure I am correct before I further discuss with him.

I have a VB6 application developed since 2003 and it's working fine in all PCs at different remote sites. Now our company has a new office and the users in there can't access the system due to connection failure to SQL Server.

The developer in the new office told me that the network configuration in there is quite weird that he sometimes experienced SQL Server connection problem with his applications too. One way he used to resolve the problem is to change the connection method to ODBC. My problem is, if my application is using ADO, it is not that easy to change to ODBC because many methods, properties, and database components are for ADO only so simply changing the connection to ODBC will not work. Am I correct?

That developer told me there is no difference.

I'm trying to write an installation program in VB6, and I need it to automatically create an ODBC connection to a SQL Server database. The only code I've been able to find on the 'net thus far is VB2 and 3 code, using things that apparently aren't available in VB6. Could someone help me with this? Here's my code, I can't get it to not give me an error.

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
(ByVal hWndParent As Long, ByVal fRequest As Long, ByVal lpszDriver _
As String, ByVal lpszAttributes As String) As Long
'ODBC Creation Procedure
Public Sub RegisterODBCDatabase(dsn As String, driver As String, _
silent As Integer, attributes As String)

Dim ret As Integer
Dim temp As String
Dim spot As Integer
Dim att As String

attributes = Replace(attributes, Chr(13), Chr(0)) ' Replace Carriage returns with nulls

attributes = attributes & Chr(0) & Chr(0) ' End of attribute section.
temp = "DSN=" & dsn & Chr(0) & attributes

ret = SQLConfigDataSource(frmMain.HWND, ODBC_ADD_DSN, driver, temp)

' ret is equal to 1 on success and 0 if there is an error.
If ret <> 1 Then
MsgBox "SQLConfigDataSource call failed"
MsgBox " SQLConfigDataSource call succeeded!"
End If
Exit Sub
End Sub
I've cut a few things out of the function (IP addresses, etc) for security purposes, so if it doesn't compile as-is, (I do realize it never creates a ODBC connection) I missed something when I was cutting, and chopped it.

Thanks in advance,
Jason DeVelvis
Shelby Computer Connection, Inc.

i have a password protected access database. i want to use ODBC with ADO. i use ADO connection as .. "DSN=DataSourceName"
but it give error when i use password protected database.
plz tell me abt it.

hi, Im Developing a System using there any way that i can add new connection inside my code instead of using the datasource(odbc) of windows thanks

Does anyone have a simple example of an ODBC API connection to a database with a select SQL statement returning rows?


How To Get All ODBC's
How do i get all the ODBC in the the system and populate a combo box with them


DB Odbc/ado
Does anybody know of a good odbc tutorial? I just need the basics. I was programming in vb with access databases a few months ago, but I forgot exactly what I did. I remember I had to like import an object something like:

Provider = "Ole.Jet.MicrosoftDatabase..."

And then I used sql queries and loops to move the information about. Any help is appreciated.

Edit: It might have been ADO and not ODBC, honestly I'm not sure.

I have a form that accesses a SQL database. Is there a way to allow the user to select the ODBC System DSN they will use (like if I populated a list box with all the DSN's on the system).


Using ODBC
I am a complete newbie when it comes to data access and VB. At work here we use a pervasive SQL database and I was interested in using sql commands to query the database. I was told the easiest way to do this was by using ODBC. I have no idea what it is or how I would go about using. I do know sql syntax and all that good stuff. Could someone point me in the direction of a tutorial or something for use of ODBC by newbies? Or if you could help me a little I would appreciate it.

Thanks in advance.

We are running a Pervasive Database and I was wondering if I could use ODBC in my VB apps to access the data. If So Where do I get the files to do this and how would I go about installing them and using them? Any help would be appreciated.

Dear all,
Please provide me the code for creating ODBC-DSN for
Microsoft Access driver(*.mdb).

please mail me if u have the code ready.

Thanks a lot.

Hey I just got the crystal report package...and Im trying to create a data source to SQL Server database. Do I need to select:




Im not sure which one...



How would I check if ODBC 3.5 or later is installed on user's system?

Copyright © 2005-08, All rights reserved