Home » VB.Net

VBA in EXCEL - Connect to SQL Server

I am a new programmer try to write some VBA codes in excel to get data out from the Sql server database. When I try to compile the codes, it said "compile error: user defined typed not defined" for the following sentences

   Dim dbsCurrent As Database
   Dim qdfPassThrough As QueryDef
   Dim qdfLocal As QueryDef
   Dim rstTopFive As Recordset
   Dim strMessage As String

What I need to define for Database,recordset or QueryDef?  Please help!!!


6 Answers Found


Answer 1

Ah.... I found an answer.  I need to add the reference of DAO object library. 

But there is another problem.......

sqlstring = "select * from customer"

connstring = "ODBC;UID=userid;PWD=password;DATABASE=db;DSN=datasource;dbDriverNoPrompt"
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("B1"), Sql:=sqlstring)
End With

It always displays the ODBC Data Sources dialog box even though I have passed all the information in the connstring.  Can anyone help please??


Answer 2

Per our support engineer:

We have Various ways to connect to a SQL server:

- Using ADODB SQL ODBC provider with DSN

- Using ADODB SQL ODBCprovider without DSN 

- ADO SQL OLEDB provider for authenticated connections

In below code example, I've used the OLEDB provider for SQL using a trusted connection.


Function Get_BCM_Connection(Optional ByVal _

        ServerName As String = _

        "Percy\MicrosoftBCM") As ADODB.Connection

'Returns an ADODB Connection object to Outlook 2003 Business Contact _

'Manager installed locally


Dim cn As New ADODB.Connection

    On Error GoTo ErrHand

     With cn

        .ConnectionString = "Provider=SQLOLEDB; " & _

            "Data Source=" & ServerName & "; " & _

            "Initial Catalog=MSBusinessContactManager;" & _

            "User ID=sa; Password=; Trusted_Connection=yes"


    End With

    Set Get_BCM_Connection = cn


    On Error Resume Next

    cn.Close: Set cn = Nothing


    Exit Function


    Set Get_BCM_Connection = Nothing

    MsgBox "Connection not propertly defined.", vbExclamation

    Resume ExitHere

End Function


And here is a KB :How To Import Data from Microsoft SQL Server into Microsoft Excel (http://support.microsoft.com/default.aspx?scid=kb;en-us;306125&sd=tech )

I hope it’s helpful.

-brenda (ISV Buddy Team)


Answer 3

Due to an overlap in communication, I have more information on this topic that I might as well post in case it helps someone.

Using VBA to Get At SQL Server Data

If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database.

Again, all the previous warnings about locking apply. Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it.

There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it.

You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database. Here's the process to create your own macro to connect to SQL Server programmatically:

1.Open Excel.

2.Click on Tools, then Macro, and then Macros...

3.Name the Macro, and then click Create.

4.In the editor window, type the following information, substituting the proper names for the server and the tables you want in between the "Sub xxxx" and "End Sub" tags:

' Declare the QueryTable object
Dim qt As QueryTable
' Set up the SQL Statement
sqlstring = "select au_fname, au_lname from authors"
' Set up the connection string, reference an ODBC connection
' There are several ways to do this
' Leave the name and password blank for NT authentication
connstring = _
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)

End With

Save and close the macro, and run it from the same menu you accessed in step 2.


There are probably at least ten programmatic ways to do this exact same thing – and all of them are correct! If you're really interested in programming using VBA, you'll find several good books here at InformIT and also on Safari.

Keeping The Database Connection Live

We now move away from the "Transfer In and Out" category to the "Linked Data" category of methods. I'll describe the two main methods to link data, both of which link Excel data into a SQL Server query.

There are ways to use Excel to manage data directly in SQL Server, but they involve a bit more programming, and I've found them to be a bit clumsy – especially for daily use. Excel simply doesn't handle locks and connectivity issues as well as you need for large-scale production solutions.

You could also code a solution that accesses data stored in an Excel spreadsheet and update a SQL Server table as a result, but this is really event-driven and not a linked solution. (Again, there's a great deal of programming help in that vein here on InformIT.)

So returning to the methods I've found easy to implement, there is a two-step process you can use to query data in an Excel spreadsheet.

First, you'll need to create a linked server. Here's how to do that:

1.Open Query Analyzer.

2.Run the following code:

-- Here we set up the linked server using the JET provider
EXEC sp_addlinkedserver N'ExcelLink', 
@srvproduct = N'', 
@provider = N'Microsoft.Jet.OLEDB.4.0', 
@datasrc = N'C:\temp\test.xls', 
@provstr = N'Excel 8.0;'


By doing so, you create a linked server, and give that connection a name. Notice also the name of the spreadsheet, which can also be a share location. You only have to do this once for each spreadsheet; if you're not going to access that spreadsheet again, it's a good idea to drop the linked server after you've used it.

Now that we have a linked server, we can access the data. The process for this method is as follows:

1.In a query tool such as Query Analyzer, type the following:

-- Setup the user connection for the spreadsheet
EXEC sp_addlinkedsrvlogin 'ExcelLink', 'false'
-- Get the spreadsheet data - SCHOOLSP is the tab name
SELECT * FROM OPENQUERY(ExcelLink, 'select * from [SCHOOLSP$]')


In this section, we've used the OPENQUERY function, which passes the query on to the provider. You'll find the query language is pretty limited at times, with Excel. If the queries aren't selective enough, set up another worksheet in the workbook with the data you want, and query that one.

As you can see, you have several options open to integrate data between Microsoft Excel and SQL Server. Keep the limitations in mind, and get that data out there!

Online Resources

These links are from Microsoft. There are several others out there but these deal with some of the issues that I brought up in the article.

Here's an article that details the data type woes with DTS and Excel imports.

This article from Microsoft details the process of using DTS with Excel.

Microsoft has a larger discussion of ODBC to Excel datatype issues here.

This Microsoft article details programming with ADO against Excel files – it shows you how to open one as a database.

-brenda (ISV Buddy Team)


Answer 4

Thanks for posing this!

You seemed to have stopped in mid stream.
Can you provide the steps for the following?:

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1

Answer 5

Thanks for posing this!

You seemed to have stopped in mid stream.
Can you provide the steps for the following?:

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1

Answer 6

A little late to help this situation but for anyone else with a similar problem of having the ODBC Data Sources dialog box open every time they execute code to return query data. I've found this issue is due to the fact that the QueryTable "Save Password" has not been set to "True" via code, or manually you can set this in the Connection Properties dialog box.

As long as you are supplying the password with code and have the "Save Password" property set to True (or checked in the dialog box) then the ODBC Data Sources dialog box should only appear the first time you execute the code, and sometimes it doesn't display even on the first time as long as you have the ODBC connection set-up on your machine.




Question:I am trying to connect an Excel workbook to a remote SQL server database and getting the following error below (see code snippet). Any insight would be appreciated. Wondering if there is an easier method to connecting then what I have. I have VBA coding in the Excel workbook (if needed, I can provide it).
Connection failed:
SQLState: '01000'
SQL Server Error: 10054
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv()).
Connection failed:
SQLState: '08501'
SQL Server Error: 11
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

I've believe I've installed everything to read Mobile SQL .SDF files.  I've insalled Sql Server Management Studio, Sql Server 2008 R2, .NET 4.0 Framework and just about everything else I've read to see the contents of an .SDF file created for what I read directly from these product sources as this DotNet Compact Framework is the greatest new SQL stuff on the planet.  I can connect using the Management Studio and see the TABLE Stucture easy enough and I can query using some simple SQL Commands from the Management Studio but what really bugs me is if this is all so wonderful, how come I cannot connect to what the SQL Server 2008 R2 Server type "SQL Server Compact" Authentication: "SQL Server Compact Authentication in Office 2010 Access, Excel or even "WOW" PowerPivot!  I've run across a couple 3rd party .SDF Readers - one that exports to Excel such as flyhoward.com  I'm growing old with all the work around import export stuff and just want to use PowerPivot to  excersise the data......or should I use something that actually works like Tableau??

I am new to SQL Server Compact and I have search MSDN for an answer to this very simple question.  I have come close to an answer but "no dice" yet.  So here is my question.  How do I create a SQL Server Compact database using VBA for EXCEL?  I have download SQL Server CE 3.5 SP2, and I have check off the box in the reference list in EXCEL for ADO 2.8 library.  I am using EXCEL 2003.  So far here is the code that I have. 

Dim pConn as ADODB.Connection

Set pConn = New ADODB.Connection
pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\test.sdf"

The code runs until it reaches "p.Conn.Open" and then VBA generates an error: "Method 'Open' of object '_Connection' failed".  Part of the problem is that I don't have any documentation in EXCEL to help me with ADO.  So I quess I have now two questions.  How do I create the database and how do I get ADO help installed in EXCEL?


Thank you for taking the time to help with this.



Peter Winson



I have create a SQL Server Compact database from VBA in Excel.  So far so good.  Now I want to access the database via a recordset.  here is my code. 


    Set recSet = New ADODB.Recordset
    recSet.LockType = adLockBatchOptimistic
    recSet.CursorType = adOpenKeyset
    Call recSet.Open("SELECT * FROM SPAR", dbConn)


When I run this I get and error in Call recSet.Open("SELECT * FROM SPAR", dbConn):Errors Occurred, [,,,,]  I have search many forums about his problem, but I still can't see where the error is from.  I am using Excel 2003, and SQL Server Compact 3.5





I was unable to connect to excel 2003 with linked server created in sql server 2008 64 bit. This was working fine with sql server 2005 32 bit.

I know that there no 64 bit drivers for JET drivers, but i am looking for some alternative ways to do the same job.

I dont want to migrate to Excel 2007.

Thanks in advance for all your help.


How to create linked Server to connect from SQL Server 2008 to SQL Server 2000.

I following the steps to create the linked server in the GUI but failed with the following error message.

I used the full computer network name as the 'Linked Server' name

 OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "Unspecified error".
OLE DB provider "SQLNCLI10" for linked server "my_server_network_name" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "my_server_network_name". The provider supports the interface, but returns a failure code when it is used.

Thanks a lot for your help.




I try to connect with PDO to my SQL Server 2008 Express.

$DBO = new PDO( "sqlsrv:(\SQLEXPRESS)", NULL, NULL, array('Database' => "mydatabase") );

I always receive this error:

Error!: [08001][Microsoft][SQL Server Native Client 10.0]SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

I can connect to the AdvantureWork expample database with:

$serverName = "(local)\sqlexpress";
$connectionOptions = array("Database"=>"AdventureWorks");

/* Connect using Windows Authentication. */
$conn = sqlsrv_connect( $serverName, $connectionOptions);


Any idea why I cannot connect with PDO?




Database error: Cannot connect ot Database: [Microsoft][SQL Server Native Client 10.0][SQL Server]Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match.
ODBC Error
Session halted.


$host = "tcp:s6s1aw1dwr.database.windows.net";
 $dbname = "internet";
 $dbuser = "skytech@s6s1aw1dwr";
 $dbpwd = "******";
 $driver = "{SQL Server Native Client 10.0}";


let me know what could be the reason ?


Hello Friends,

I am not able to connect to SQL Azure Server from my local SQL Server Management Studio 2008 R2 . I am getting the following error.

Cannot connect to xxxxxx.database.windows.net.


A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or

was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

Error Details


A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=53&LinkId=20476

Error Number: 53
Severity: 20
State: 0

Program Location:

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.DatabasePicker.FillSqlServerDbNames()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.DatabasePicker.BeginConnection()
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.DatabasePicker.PickDatabase(IWin32Window parent)


I got some help from net and changes few settings, but still I am getting the same error.

Please let me know, if you have any solutions.

Thanks in Advance.


Chiranjibi Das



I have a already purchase domain name, space, and database (SQL SERVER 2005) for stknp.com.

problem is that I can not connect asp.net page to sql server 2005 using sql server authentication.

If I was creating a database on local server then it is running. what should I change in web congfig file and any others?

Please give me code. I have never connect asp.net page to sql server online (Sql server authontication mode).

please create one page of simple one field to submit in database and please send me it in my email id nileshgupta52@gmail.com or contact me on this id



Nilesh Gupta

India (Lucknow)


I have 2 identical brand new servers Running Windows Server 2008 R2 not on a domain but simple LAN. I Installed a default instance of SQL server 2008 R2 enterprise on each one with all the default settings. No Problems during the Installaion. I Added the Windows Administrators Group and Users group to the SQL admins role during the SQL Server Installation. Remote connections are enabled on each server. I restored the same database from a copy on each Installation. Configured the user accounts on each maching and made them sysadmins and granted permissions where they were needed. Now from server A when i try to establish a connection to Server B from SQl server Management Studio I get An error stating that the server was not found or accesible. keep in mind i can \\server B\c$ and vice versa, named pipes are enabled. I have never had this problem before in my life. And it's happening on both servers. I really need each installation of SQL to be able to see the other remote installation as i have to create a mirror.




I have problem connecting to reporting services from SSMS, it used to work before, but recently in the rsreportserver.config file <UrlRoot>http........</UrlRoot> was changed to a different one. After that I had problem connecting to reporting services. We need that URL retained in the config file, if anyone know any work around to get into SSRS, please let me know.

I am getting an error that, the remote name could not be resolved: 'xxxxx.xxx.com' (Here it is showing the new URL that is in the config file)

We are using Reporting Services 2005


OLE DB provider "SQLNCLI" for linked server "xx.xxx.xxx.xxx" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure reminder, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "xx.xxx.xxx.xxx" was unable to begin a distributed transaction.

OLE DB provider "SQLNCLI" for linked server "xx.xxx.xxx.xxx" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Procedure reminder, Line 5

The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "xx.xxx.xxx.xxx" was unable to begin a distributed transaction.

I'm hoping to connect an SQL CE file (main.dat) to an Access DB so I can run some vba code which:

1. Uses DAO to open a recordset (a table in the SQL CE DB)
2. Uses a field containing paths to mp3 files to extract the id3 tag data
3. Update an existing field from the same table/Same SQL CE DB

I've looked around and can't seem to find any ODBC driver(s) for SQL CE.  Got some info from Erik who suggested I post here.

Thanks in advance!

EDIT:  I just now was able to get Erik's code to work in Excel from my DB.  But I'm not sure how to send it back to the SQL CE DB from VBA... Is it possible?


I upgraded my Windows Server 2003 x64 to Windows Server 2008 R2 x64, and I noticed that I cannot connect to SQL Server 2008 (from within the LAN) which is running on the computer.

I ran the OpenSqlServerPort.bat script specified on http://support.microsoft.com/kb/968872/en-us, however the software Firewall is still somehow blocking access from within my LAN. I installed Microsoft's Port Query tool, and it verifies that the port is being FILTERED:

TCP port 1433 (ms-sql-s service): FILTERED
portqry.exe -n -e 1433 -p TCP exits with return code 0x00000002.

I can also see in the EventLog it's being blocked:

The Windows Filtering Platform has blocked a packet.
Filter Information: Filter Run-Time ID: 74459 Layer Name: Transport Layer Run-Time ID: 12

I wish there were a way to lookup the filter rule which is causing the block. I did not find anything using the run-time ID on Google.

Anyone have any ideas?


Have installed the SQL Server Migreation Assistant 2008 on my Windows Server.  I am able to connect to an Oracle DB located on another server no problem.  When I try to "Connect to SQL Server" with server name entered, server port as default, database name correct and Window Authentication selected the "Connect" button remains grayed out unavailable.  The SQL Server is located on the same Windows Server the SSMA tool is installed on.  Any help greatly appreciated, Roscoe


I am trying to do something fairly simple.. allow my user to import thier data from Excel into SQL Server, where it can be used by the application.

To do this, I'm using the fairly simple code that is common everywhere I look:

strFilePath = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fdlgFileOpen.FileName + ";Extended Properties=Excel 8.0"
connExcel = New OleDbConnection(strFilePath)
Dim ExcelCommand As New OleDbCommand("INSERT INTO [ODBC;Driver={SQL Server};Server=servername _
    \sqlservername;Database=MyriadSQL;Trusted_Connection=Yes;].[tblCostCodes] (CC_L1, CC_L2, CC_L3, CC_L4, CC_L5, _     
    CC_Description) SELECT * FROM [CostCodes$];", connExcel)
Catch ex As Exception
End Try

Now, on my development computer this code works perfectly.  The data imports, and I can go on my merry way.

On the TEST computer, this code throws the error: "ODBC -- Connection to {SQL Server} servername\sqlservername  failed."  This seems very odd to me, because I'm using exactly the same SQL Server and database to interact with the application, and all the other screens in the application work perfectly.  The test computer CAN connect to this SQL Server, but it keeps throwing this error whenever I try import data from Excel.

I've tried everything I can think of - using the server's IP address instead of the server name, sending the SQL Server administrator data along with the request, taking out the Trusted_Connection=Yes... nothing works.  What is going on here?  How can this code work fine on my computer, and hiccup on another?

Thank you for your help,



I am new to SQL Server 2008 Developer edition for class online. I do not know my computer name/ server name to connect to server to start a database project. Please help me to find the info to open the database. Assignment due 4/5/2010. My computer is a Dell wireless laptop with Windows XP Home edition. I am getting error 53 and 40. Cannot connect to server and named pipes provider.

The error message I got was: ...A sever was not found or accessable. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: Named Pipes provider error 40- could not open connection to SQL Server). (Microsoft SQL Server error 53).

I know what I named my computer but I don't know if that is the name I use to connect to the Server and I don't know what the server name would be. Where would I find that and what do I enter into the dialog box to establish a connection? I have not set up any database or server in the Database Engine or other areas of this program. This is a brand new copy I purchased and installed recently.


Can anyone help me to recover VBA password of a Excel file or Excel add in file.
I am in search of VBA code that will help me to recover the VBA password of password enabled excel file.

Hi,  I am currently testing a migration from SQL 2000 to SQL 2005.  We have a test server running SQL Server 2005, and production server running SQL 2000.

I currently use some excel VBA to query the database to update or retrieve data.  I do this on my PC at my desk, with SQL server being on a separate server.  The SQL 2000 connection string looks like this:

conn1.Open "Driver={SQL Server};Network=DBMSSOCN;server=xxx.xx.x.x; database=mydb"
We don't specify a uid and pwd as it uses windows authentication.
This same string works when I connect to the SQL Server 2005 database.

I would like to utilize SQL Native Client (wondering if there would be a performance increase) by using the string:

conn1.Open "Driver={SQL Native Client};Network=DBMSSOCN;server=xxx.xx.x.x; database=mydb".  This gives me the error  "Log-in failed for user ''.  The user is not associated with a trusted SQL Server connection.

My next option was to create a SQL user id, call it ACil, and then use the string
conn1.Open "Driver={SQL Native Client};Network=DBMSSOCN;server=xxx.xx.x.x; database=mydb; uid=ACil; pwd=hello". 
This gives me the error  "Login failed for user ACil.  Reason:  The password for this account must be changed".

I assigned the name as db_owner, tried defaulting to different databases for the user, creating another new user, but I always get is this error.  Does anyone know what could be causing it?


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure