Free Microsoft Excel 2013 Quick Reference

Odbc excel driver error Results

Dear All,

I have a problem about import external data from oracle database to
excel spreadsheet.

Two weeks ago, i import data from oracle database to excel spreadsheet
using import external data menu, and then i save it to my harddisk.

Yesterday, when open that file, i got error message "[Microsoft] [ODBC
driver Manager] data source name not found and no default driver

after that i configure again my odbc and open excel file, but the
problem still same.

note : before i using oracle 8i client when importing data. Now i using
oracle 9 client.

Do u have any idea ?



I would like to access from Excel 2003 (OS: Windows XP) to a SQLite-Database
with ODBC.

I did the following steps:

1.) Install SQLite-ODBC-driver:

From I downloaded the file
"sqliteodbc.exe" which installs the ODBC-driver on my computer. I saw after
the installation on the "ODBC-datasources/System-DSN"-menu three new entries
("SQLite Datasource", "SQLite UTF-8 Datasource" und "SQLite3 Datasource").

2.) Create an SQLite-database:

From I got a SQLiteBrowser
(version 1.2.1) and I created one new database ("TestDB") with three tables
and I put few data inside. I saved the database under "C:Test.db".

3.) Create USer-DSN:

On "ODBC-datasources/User-DSN" I accomplished the following steps:


"SQLite ODBC Driver"


Data Source Name: TestDB

Database Name: C:Test.db


4.) Get the data in Excel:

In Excel I made the following:

"Data/Import external data/Import data.."

"+Create new datasource.odc" + "Open"

"ODBC DSN" + "continue"

"TestDB" + "continue"

(mask "SQLite ODBC Driver Connect" appears)


(mask " Dataconnectionproperties " appears)


(Maske "SQLite ODBC Driver Connect" appears again)

When I push "OK" I get an error message that I have no access and so on.

Alternatively I accomplished step 1.) until 3.) und wrote the following code
in VBA:

Sub TestDB()

Dim ws As Workspace

Dim con As Connection

Dim rs As Recordset

Set ws = DBEngine.CreateWorkspace("ws1", "", "", dbUseODBC)

Workspaces.Append ws

Set con = ws.OpenConnection("con1", dbDriverComplete, False,
"ODBC;DSN=TestDB") 'Test = DSN-Name

Set rs = con.OpenRecordset("select * from TabPerson")

' Enumerate the specified Recordset object.

With rs

Do While Not .EOF

'Debug.Print , .Fields(0), .Fields(1)

MsgBox .Fields(0)



End With




End Sub

When I call this procedure I get the following error message:

"Run time error '3146'

What is wrong?

Thanks a lot


I have a vba addin that was originally created in DAO format and it is really quick, problem is that this addin needs to be shared to a lot of ppl and we need to a lot of setup to get DAO to function correctly within Excel 2007, so I am trying to switch the addin to ADO. The speed in ADO is terrible compared to the DAO. The DAO does 4800 records in about 36 seconds and ADO is doing the same amount of records in 30 minutes.

DAO setup:
Option Explicit
Dim systemname
Dim sqlstatement
Dim db As DAO.Database
Dim wk As DAO.Workspace
Dim myerror As Error
Dim connStr
Dim isopen As Boolean

Sub openconnection()
'assign systemname, sqlstatement and connection string

If isopen = True Then
    Exit Sub
End If

systemname = "database"

connStr = "ODBC;DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=" & systemname & _
    ";CMT=0;DBQ=QGPL;NAM=0;DFT=0;DSP=0;TFT=0;TSP=0;DEC=0;XDYNAM" & _
& _

'initialize database stuff
Set wk = CreateWorkspace("", "", "", DAO.dbUseODBC)
wk.DefaultCursorDriver = dbUseODBC

Set db = wk.OpenDatabase("", False, False, connStr)
isopen = True

End Sub
Current ADO setup:
Sub openconnection3()
'If Isopen = True Then
'    Exit Sub
'End If

'Open Connection to System
Set cnn = New ADODB.Connection
cnn.Properties("Prompt") = adPromptComplete
sConnString = "Driver={Client Access ODBC Driver (32-bit)};System=database;"
cnn.Open sConnString

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
'Isopen = True
Set rs = New ADODB.Recordset
End Sub
I could like to get the if statement working if I can

Function call for DAO:
Function GetDCQOH(Line, Item) As Double

Dim rstRecordset As DAO.Recordset

On Error GoTo errors

'On Error GoTo trap


sqlstatement = _
    "SELECT iqtyoh from database where ILINE = '" & Line & "' and IITEM# = '" & Item &

Set rstRecordset = db.OpenRecordset(sqlstatement, DAO.dbOpenSnapshot)

'get the data
GetDCQOH = rstRecordset.Fields(0).Value

'close the connections and clean up
Set rstRecordset = Nothing

GoTo noerrors

'MsgBox Error


End Function
ADO Function call:
Function GetQOO(Line, Item) As Double

Dim sSQL As String

On Error GoTo errors

'On Error GoTo trap


sSQL = _
    "SELECT iqtyoo from database where ILINE = '" & Line & "' and IITEM# = '" & Item &

rs.Open sSQL, cnn, adOpenKeyset, adLockOptimistic

'get the data
GetQOO = rs.Fields(0).Value

'close the connections and clean up
Set rs = Nothing

GoTo noerrors

'MsgBox Error


End Function

Any help will be greatly appreciated!

Hi All,

I have the below macro which I have recorded for importing the data from SQL Server table. I have a userform in which I have list box I am populating that listbox with some values from another sql server table. Now what I am doing is that while importing the data from sql table I select a criteria now I want the user to select multiple items in listbox and my below macro should consider that selection and extract the data according. As of now below mentioned code works fine with single selection in listbox but when I select multiple items in my listbox. It shows error. Please help.

Sub sqldataextract()
Dim Product As String
Dim CostElement As String
CostElement = frmwarehouse.TextBox1.Value
Product = frmwarehouse.ListBox4.Value
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DRIVER=SQL Native Client;SERVER=XXXXXXXXX;UID=admin;PWD=*****;APP=Microsoft Office XP;WSID=XXXXXXXX"
        ), Array(";DATABASE=meta_data;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT mydata.CAC, mydata.Year, mydata.""Cost Element"", mydata.""Cost Element
Name"", mydata.Name, mydata.""Cost Center"", mydata.""Company Code"",
mydata.""Unique Indentifier 1"", ""Cost Center mapping"".""Produ" _
        , _
        "ct UBR Code"", ""Cost Element Mapping"".FSI_LINE2_code" & Chr(13) &
"" & Chr(10) & "FROM sap_data.dbo.""Cost Center mapping"" ""Cost Center
mapping"", sap_data.dbo.""Cost Element Mapping"" ""Cost Element Mapping"",
sap_data.dbo.mydata myda" _
        , _
        "ta" & Chr(13) & "" & Chr(10) & "WHERE mydata.""Unique Indentifier
1"" = ""Cost Element Mapping"".CE_SR_NO AND mydata.""Cost Center"" =
""Cost Center mapping"".""Cost Center"" AND ((""Cost Center
mapping"".""Product UBR Code""='" & Product & "') AND (""" _
        , "Cost Element Mapping"".FSI_LINE2_code='" & CostElement & "'))")
        .Name = "Query from mydatanew"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
Currently my listbox's MultiSelect property is set to 0. If I set it to 1 or 2 the above macro doesn't work.

Thanks for your help in advance.

Hi there,

I'm looking for help with a macro I'm trying to write to loop through all the excel files in a given folder and then combine the data into one pivot table. Since upgrading to Excel 2007, I've found that the pivot table methods have changed somewhat and now when I try to run the macro, I get a dialog box asking for the driver name. Any suggestions would be greatly appreciated.

Many thanks,


Option Explicit

Sub myReporting()

Application.ScreenUpdating = False
Application.EnableEvents = False

Dim myDefaultPath As String
Dim myDataPaths() As String
Dim myConnection As String
Dim mySQL As String
Dim mySheets As String
Dim i As Long
Dim myReportPath As String

Dim myFindFiles As Variant

On Error Resume Next

mySheets = "`.`Sheet1$` `Sheet1$`"
myDefaultPath = "C:Reporting"
myReportPath = "C:Reports.xls"

'search reporting folder for all monthly paid and unpaid sheets
myFindFiles = myListFiles("C:Reporting", "*.xls")

'populate myDataPaths array with all filenames
If TypeName(myFindFiles) <> "Boolean" Then
    ReDim Preserve myDataPaths(UBound(myFindFiles))
    For i = LBound(myFindFiles) To UBound(myFindFiles)
        myDataPaths(i) = myFindFiles(i)
    Next i
    MsgBox ("No reporting data exists")
    ActiveWorkbook.Close False
    Exit Sub
End If

'create connection string
myConnection = ""
myConnection = myConnection & "ODBC;DSN=Excel Files;DBQ=" & myDefaultPath & myDataPaths(0)
myConnection = myConnection & ";DefaultDir=" & myDefaultPath
myConnection = myConnection & ";DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"

'set SQL for 1st found file
mySQL = ""
mySQL = mySQL & "SELECT *" & Chr(13)
mySQL = mySQL & "FROM `" & myDefaultPath & myDataPaths(0) & mySheets

'add SQL for each additional found file
For i = 1 To UBound(myDataPaths)
    mySQL = mySQL & Chr(13) & "UNION ALL" & Chr(13) & "SELECT *" & Chr(13)
    mySQL = mySQL & "FROM `" & myDefaultPath & myDataPaths(i) & mySheets
Next i

    ActiveWorkbook.Connections.Add "Query from Excel Files1", "", myConnection, mySQL, xlCmdSql
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ActiveWorkbook.Connections("Query from Excel Files1"), Version:= _
    xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R1C1", _
    TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion12

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Function myListFiles(myFolder As String, Optional myFilter As String = "*.txt") As Variant

Dim myFoundFiles As String, myTempFile As String

myFoundFiles = Dir(myFolder & myFilter)

If myFoundFiles = "" Then
    myListFiles = False
    Exit Function
End If

    myTempFile = Dir
    If myTempFile = "" Then
        Exit Do
    End If
    myFoundFiles = myFoundFiles & "|" & myTempFile

myListFiles = Split(myFoundFiles, "|")

End Function

Anyone can help ? i'm being staring at the insert statment very long. i can't see where went wrong?

    Dim cn As ADODB.connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    On Error GoTo test_Error
    'Location of the excel files
    Set cn = New ADODB.connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:Documents and SettingswakaDesktoptest.xls;" & _
    "Extended Properties=Excel 8.0;"
    strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
    "Server=d16;Database=dbtest;" & _
    "UID=costlyme;PWD=myass99].TestDB " & _
    "Select * FROM [insert$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print " Records affected: " & lngRecsAff
    Set cn = Nothing
    On Error GoTo 0
    Exit Sub
     MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of
VBA Document  ThisWorkbook"
End Sub
i got this error(screenshot)

I am trying to change the SQL command to an odbc database connection

Sub Macro1()
' Macro1 Macro

    With ActiveWorkbook.Connections("YearBS").ODBCConnection
        .BackgroundQuery = False
        .CommandText = Array( _
        "sp_report BalanceSheetStandard show Label, Amount parameters DateFrom = {d'2009-01-01'}, DateTo =
{d'2010-12-31'}, " _
        , "SummarizeColumnsBy = 'Month'")
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DSN=QuickBooks Data;DFQ=I:ACCOUNTINGQuickbooksRed Tail Networks,
Inc.QBW;SERVER=QODBC;OptimizerDBFolder=%UserProfile%QODBC " _
        ), Array( _
        "Driver for QuickBooksOptimizer;OptimizerAllowDirtyReads=N;SyncFromOtherTables=N" _
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("YearBS")
        .Name = "YearBS"
        .Description = ""
    End With
End Sub
I recorded that in excel and then when I try to run it I get an error of:
Application-defined or object-defined error When I debug it highlights this line:
        .CommandText = Array( _
        "sp_report BalanceSheetStandard show Label, Amount parameters DateFrom = {d'2009-01-01'}, DateTo =
{d'2010-12-31'}, " _
        , "SummarizeColumnsBy = 'Month'")
Any help would be appreciated thank you.


I want to add the following 2 'conditions' to the query at the bottom (which works):
1) AND BLH_REMARK NOT LIKE %something%

By trial and error I've tried several syntax's but I always get errors back while typing. What's the correct way to implement those 2 AND's ?

    With Selection.QueryTable
        .Connection = _
        "ODBC;DRIVER=SQL Server;SERVER=BLAH-server;UID=user;PWD=password;APP=Microsoft®
        .Sql = Array( _
        , _
        , _
        "BLAH.BLH_UNIT_ID" & Chr(13) & "" & Chr(10) & " FROM database.dbo.BLAH BLAH,
" _
        , _
        "database.dbo.MODELE MODELE " & Chr(13) & "" & Chr(10) & "WHERE
        , _
        "((BLAH.BLH_DATE>={ts '" & DateFrmStr & "'})" _
        , _
        "and(BLAH.BLH_DATE<={ts '" & DatetoStr & "'}) " _
        , _
        "AND(BLAH.BLH_FAX_DATE IS NULL))" & Chr(13) & "" & Chr(10) & "ORDER BY
       .Refresh False
    End With
It's driving me nuts.. and I don't really have the time to dig deeper into all this, so thanks in advance!

I have a bunch of Excel files that link to accounting software (Timberline) via ODBC. We just changed Timberline's database name. When I refresh the data in my spreadsheets, Excel prompts for my login and password (as usual) and after entering, I get the following errors:

1) No Such Database
and then after clicking OK on that error, I get:
2) Driver's SQLSetConnectAttr failed

After clicking OK on this error, it takes me to a "Select Data Source" dialogue box. I select the new database but then get the same error.

Any ideas as to what I'm doing wrong?

Part of the problem is that the old database name no longer exists but I have been unable to point the query to the new database.

I am also unable to edit the query. It takes me through the same steps as above.

(Running Excel 2003 on Windows XP with service pack 2 installed.)

I have a problem with my excel add-in which works fine for earlier versions of excel - but does not work properly with excel 2007. The OP system is windows XP.

The code to connect is as follows:
Public cs_dbs As Database
Set cs_dbs = OpenDatabase(dbName, False, True, "ODBC;Regional=Yes")

When dbName is empty (as it will be for a new file), the user will be prompted to select an ODBC source. After I select the source I get the following error message: Cannot open MyDB database. Error: ODBC--connection to 'MyDBSQL2005' failed.

If I look in the odbc trace log, I will eventually see the following error: DIAG [S1000] [Microsoft][ODBC Driver Manager] General error: invalid window handle.

Trying to connect to a Progress database, also ends in failure after I select an odbc source. In this case I should see a window prompting me for a password but I just get the error message that the operation was cancelled by the user.

BUT: The following will work:
Public cs_dbs As Database
Set cs_dbs = OpenDatabase(dbName, False, True, "ODBC;Regional=Yes")

In debug mode, I can connect to both DB types. The odbc trace logs show no errors.

It could be a bug in Excel 2007. This add-in works in earlier versions of excel and works in debug mode in excel 2007.
AND if I open an existing excel sheet which already uses the add-in, then I can connect without problems to the database. The DB is opened automatically & formulas are refreshed.

Does anybody have any clue what is going wrong here? I hope I have provided enough information.

I have a worksheet with a Pivot Table whose source data is an Access Database
encrypted using a Workgroup File (.mdw). I created a User DSN within the
OBDC Object Administrator and I specified the .mdw file. I created the
PivotTable by pointing to my DSN data source and I created the new Pivot
Table succesfully.

I moved the files (.mdb .mdw and .xls) to another computer with the same
versions of Windows and Office and recreated the DSN entry but now I can not
refresh the data. I get an error message saying "OBDC Microsoft Access
Driver Login Failed. Cannot start your application. The workgroup
information file is missing or opened exclusively by another user". What is
wrong? How can I check the workgroup file used in the original pivottable in
VBA and how can I change it?

I'm trying to Import Data into Excel from an Access Query that uses the Nz
function. I'm getting an error before the data even appears in Excel. Error
is "[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Nz' in

I've read the email string submittted by DEI on 10/17/05 but think this is a
different problem. Any ideas how I can overcome the error?


I am trying to connect to an existing Excel 97 spreadsheet.

I have found the following connection strings from I have tried both
versions (seperately) and I get the same error message 1004: application-defined or object-defined

I have been able to use this method to connect to an external Oracle database using ODBC.

Is there something wrong with my coonection string? BTW ThisSheet is declared as Excel.Worksheet
and definitely exists.

ThisSheet.QueryTables.add Connection:="Driver={Microsoft Excel Driver
(*.xls)};DriverId=790;Dbq=D:MonthlyStatsMaster.xls;DefaultDir=D:;", _
' Destination:=ThisSheet.Range("A1"), Sql:="Select * from [Hierarchy$]"

ThisSheet.QueryTables.add Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:MonthlyStatsMaster.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""", _
Destination:=ThisSheet.Range("A1"), Sql:="Select * from [Hierarchy$]"

Nirmal Singh


Hi there. I am trying to query a text file (via MS Query) such that the
first field contains a date represented as a string (e.g. '2005-05-02
12:00:00'). I want this field to be represented in excel as datetime.

My MSQuery is like:

SELECT cast('2005-05-02 12:00:00' as date) as MyDatetime FROM
`C:TEMPPerfdataTest`FspEbsApps1_AllWithHdr.csv FspEbsApps1_AllWithHdr
(via ODBC Text driver)

but returns the following error:

syntax error (missing operator) in query expression 'cast('2005-05-02
12:00:00' as date)'

I have tried replacing 'as date' with 'as sql_date'; 'as sql_c_date'; 'as
sql_timestamp'; etc, etc.

Help - please if anybody could offer some advice around MSQuery/ODBC
CAST/CONVERT function syntax I'd be immensly grateful.


Neil Evans-Mudie
-. . .. .-.. / .----. ... --- -. .. -.-. .----. / . ...- .- -.
.... -....- -- ..- -.. .. .

e: address is a spam sink
If you wish to email me, try neilevans underscore mudie at hotmail dot com

I am using Office 2003 on Windows XP.

I am running the following code. This is running from a standard code module
in Excel, querying Oracle, and making a table into an Access DB. The problem
is, sometimes it runs fine, other times I get "ODBC Call Fails" error:

Dim sSQL as String
Dim sConnect as String
Dim sFullNameDB as String


sFullNameDB = "C:TempMyAccessDB.mdb"

sSQL = sSQL & "[GL].ACCTNO, "
sSQL = sSQL & "INTO "
sSQL = sSQL & "[IMPORT] "
sSQL = sSQL & "FROM "
sSQL = sSQL & "[ODBC;" & sConnect & "].[APPS.GLBALVW] AS [GL] "
sSQL = sSQL & "WHERE "
sSQL = sSQL & "[GL].PERIOD_NAME IN ('Sep-03', 'Sep-04') AND "
sSQL = sSQL & "[GL].FUND BETWEEN '800' AND '999' AND "
sSQL = sSQL & "[GL].ACCOUNT BETWEEN '0000' AND '8999';"

Set cnADO = New ADODB.Connection
cnADO.CommandTimeout = 0
cnADO.CursorLocation = adUseClient
cnADO.Provider = "Microsoft.Jet.OLEDB.4.0"
cnADO.ConnectionString = sFullNameDB
cnADO.Execute sSQL
Set cnADO = Nothing

Please note I have timeout set to zero.
Any help appreciated. Thanks in advance.

Hi folks, I am having a problem with Excel and I hope somebody here can give me some help.

I want to connect to a database through Excel using MS Query. However I am getting the following error message when I attempt to do this:-

"Data source name not found and no default driver specified"

Anybody got any deas on why this error message is happening?


I'm not sure this is the right forum for this thread, but hopefully the Admins can move it if it isnt.

Here is my situation.

I had a web server with a web page that referenced an Excel spreadsheet.
(a href="c:webservernamexyz.xls" target=main)

there is a query to retreive data in the ss from a Access Database.

I had to move the website from one server to another, which included moving the data from an Access Database to a SQL 2000 Database, which sits on a SQL Server.

There is a DSN created on the web server for the connection.
From the web server, I can open the Excel Sheet and it queries the database fine.

My issue is that now when someone hits the webpage from their computer, they get the spreadsheet, but since they dont have a DSN on thier machine, the get an error stating "[Microsoft][ODBC Driver Manager] Datasource name not found and no default driver specified."

Is there a way to specify to use the DSN on the web server so I wouldnt have to create a DSN on every machine that would need to access it?


i am trying to link an access table into excel by using the Menu item:
Data-->get external data--> new database query. My problem is that i keep
getting the error "[Microsoft][ODBC Microsoft Access Driver]invalid datetime
format on column number 1 (date/time). My date/time should come in like the
table has it, 12/2/2005 15:45, but it will not go through with the query. i
have tried it with the date time separated into different columns and it will
work, and it will show 12/2/2005 0:00 for the date column and a wrong date in
front of all of the times.
Regaurdless, I need them grouped together, but just wanted to see if it
worked the other way. Can anyone help with this or know why this is

I have a spreadsheet that is saved into GroupWise DMS. Each time you open the
..xls and make simple formatting changes, then Save and re-open, the changes
aren't there. I saved a local copy of this .xls, but formatting changes still
did not save.

I tried a Detect & Repair and received
"Error 1919: Error configuring ODBC data source: MS Access database: ODBC
Error 6, Component not found in the registry."

When checking ODBC properties in Admin Tools, I receive error:
"The setup routines for MS Access driver (.mdb) ODBC driver cannot be found.
Please reinstall the driver."

As if you haven't seen this before: HELP!

Please somebody I need help and I am desperate!
I have and ODBC Query with the following SQL code


I want to change the E_PERIOD criteria from the 2 to something else and I want VBA to pass that change through;
BUT, I cannot turn the criteria to a parameter interface (i.e such as "=?") as the OMNIS databse cannot recognise that and returns an error which I was told is a restriction on that Driver??

So what I want to do is VBA code in this fashion;

Sub CreateParam()

Dim oQuery As QueryTable
Dim oParam As Parameter

Set oQuery = empODBC.QueryTables("OMNIS.EMP_RECORD")

oQuery.CommandText = Replace(oQuery.CommandText, "=2", "=1")


End Sub

However I get an error on the line
Set oQuery = empODBC.QueryTables("OMNIS.EMP_RECORD")

it is Runtime 424 Object Required.... what am I doing wrong or how should that command line be constructed?.

Please help me!

No luck finding an answer? You could always try Google.