Excel VBA can connect to a database on Windows by using a data provider, opening a connection, and sending SQL through ADO (ActiveX Data Objects). A common pattern is to create an ADO connection in VBA, run a read-only query, and place the returned records on a worksheet; the provider, server details, permissions, and security settings must match the database you are using.
What a VBA database connection does
A connection lets a VBA procedure exchange data with a database without requiring you to copy and paste results manually. The macro can read records, add or update records, or run a stored procedure, depending on the database account’s permissions and the SQL it sends. Excel itself is not the database: it acts as a client, while a database management system such as Microsoft Access or SQL Server stores and manages the data.
ADO is a programming interface rather than a database or a driver. It gives VBA objects such as a connection, command, and recordset. A separate provider or ODBC driver translates the request for the particular database. This distinction explains a frequent error: the VBA code can be syntactically correct but still fail because the required provider is not installed, the connection details are wrong, or the account cannot access the database.
Choose the database and connection method
First identify where the data lives and what access the database administrator allows. A local Access file and a company SQL Server do not use the same connection string or necessarily the same provider. Common choices include:
- Access: A file such as an
.accdbis commonly accessed through the Microsoft ACE OLE DB provider. The provider’s availability and 32-bit or 64-bit architecture must be compatible with the Office installation. - SQL Server: A server connection commonly uses Microsoft OLE DB Driver for SQL Server or an ODBC driver. The server name, database name, authentication method, and encryption requirements come from the database administrator.
- Other database systems: These may offer an ODBC driver or their own provider. Use the vendor’s current documentation for its driver name and connection-string options rather than assuming a SQL Server or Access example will work.
ADO is convenient for VBA because it exposes database operations as objects. ODBC is another connection technology, and an ODBC driver can also be used with ADO through the OLE DB provider for ODBC, or from other supported tools. Do not confuse an ODBC data source name (DSN) with a driver: a DSN stores connection settings, while the driver performs the database-specific communication. A DSN-less connection can put settings in code, but it does not remove the need for a compatible driver.
Example: connect to SQL Server with ADO
This Windows example uses late binding: CreateObject creates ADO objects at runtime, so you do not need to set an ADO reference in the VBA editor. It assumes the Microsoft OLE DB Driver for SQL Server is installed, that the Windows user has permission to read the database, and that the workbook contains a worksheet named Data. Replace the server, database, and table names with valid values for your environment.
Private Sub LoadOrders()
Dim cn As Object
Dim rs As Object
Dim ws As Worksheet
Dim sql As String
Dim fieldIndex As Long
On Error GoTo Failed
Set ws = ThisWorkbook.Worksheets("Data")
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' Use Windows authentication; replace the server and database names.
cn.Open "Provider=MSOLEDBSQL;" & _
"Server=YOUR_SERVER;" & _
"Database=YOUR_DATABASE;" & _
"Integrated Security=SSPI;" & _
"Encrypt=Yes;"
sql = "SELECT TOP 1000 OrderID, OrderDate, CustomerID " & _
"FROM dbo.Orders ORDER BY OrderDate DESC;"
' Forward-only cursor and read-only lock are suitable for reading results.
rs.Open sql, cn, 0, 1
For fieldIndex = 0 To rs.Fields.Count - 1
ws.Cells(1, fieldIndex + 1).Value = rs.Fields(fieldIndex).Name
Next fieldIndex
If Not rs.EOF Then
ws.Cells(2, 1).CopyFromRecordset rs
End If
CleanUp:
On Error Resume Next
If Not rs Is Nothing Then
If rs.State <> 0 Then rs.Close
End If
If Not cn Is Nothing Then
If cn.State <> 0 Then cn.Close
End If
Set rs = Nothing
Set cn = Nothing
Exit Sub
Failed:
MsgBox "Database load failed: " & Err.Description, vbExclamation
Resume CleanUp
End Sub
The macro writes field names across row 1 and any returned records starting in A2. CopyFromRecordset advances through the recordset as it copies it. The query limits the result to 1,000 rows as a basic safeguard; change the limit only if the database and worksheet can handle the larger result. If the query returns no records, the headers still appear.
The code does not clear old worksheet values. If a later query returns fewer rows or columns, data from an earlier run could remain below or to the right of the new results. Decide which output area the macro owns and clear that area deliberately before loading, or write results to a new sheet. Avoid clearing an entire sheet if it might contain formulas or user-entered information.
Connection strings, authentication, and drivers
A connection string tells the provider how to reach the database and authenticate. In the example, Server and Database identify the SQL Server destination, while Integrated Security=SSPI requests Windows authentication. That works only when the Windows account running Excel is authorized. If the database uses another approved authentication method, follow the administrator’s instructions and the provider’s documented syntax; do not put a real password into a workbook that other people can open.
Encrypt=Yes requests an encrypted SQL Server connection. Encryption and certificate validation depend on the installed provider and server configuration. Do not casually disable certificate checks to make an error disappear; ask the database administrator to resolve certificate or trust problems. The provider name MSOLEDBSQL also has to be installed on the computer running the macro. If Excel reports that it cannot find the provider, check the driver installation and whether its architecture is compatible with the Office installation.
For an Access database, the provider and file path differ; a SQL Server connection string is not a substitute. For example, a commonly used ACE provider string identifies an .accdb file with a Data Source value. ACE installation and Office bitness can complicate setup, particularly on computers with multiple Office components. Confirm the supported installation route for the organization before installing database components.
Queries, parameters, and safe updates
Keep SQL readable and narrowly scoped. Select only the columns and rows needed rather than retrieving every field from a large table. A fixed query, like the example, is easy to inspect, but a query that changes based on worksheet values needs extra care. Joining user input directly into SQL text can cause errors and may expose the database to SQL injection, especially when a workbook is shared or accepts input from users.
For variable values, use an ADO command with parameters instead of assembling a query by concatenating cell contents. A parameterized SQL Server command can use a question-mark placeholder and append a parameter with the appropriate data type and size. With OLE DB, parameter order matters; the placeholder is positional even if the parameter is given a descriptive name in VBA. Match the parameter type to the database column, validate the worksheet value, and consult the provider’s documentation for supported types.
Reading data is safer to start with than editing it. An update or delete query changes the shared database and usually cannot be reversed with Excel’s Undo command. Before enabling writes, test against a non-production database, limit the account’s permissions, validate every input, and consider using a database transaction or a reviewed stored procedure. Granting a workbook broad administrator privileges is not a good workaround for an access-denied error.
Run the macro and handle errors
In Excel for Microsoft 365 on Windows, save a macro-enabled workbook as .xlsm, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste in the procedure. On Mac, the Visual Basic Editor shortcut differs by keyboard and Excel configuration; the editor can also be opened from the Developer tab. Excel for the web can open a workbook containing macros but cannot run VBA macros.
Run only macros you trust. Excel may block macros from files downloaded from the internet or display a security warning, depending on policy and file origin. Do not weaken macro security globally just to run one workbook; follow your organization’s approved process. Save a separate copy of the workbook before running a macro, because VBA actions cannot be undone with Ctrl+Z. On Mac, use Command+Z for ordinary worksheet undo, but that does not make a completed VBA action reversible.
The example has a basic error handler that displays the provider’s error message and closes objects. Such messages can reveal whether the problem is a missing provider, an unreachable server, a rejected login, or invalid SQL, although the exact wording varies. For troubleshooting, check the server and database names, network or VPN access, driver installation, credentials, permissions, certificate configuration, and the SQL table and column names. Avoid logging passwords or sensitive query results in worksheets or error messages.
Windows, Mac, and Excel for the web
The code above targets Excel for Microsoft 365 on Windows, where ADO and Windows database providers are commonly used with VBA. Excel for Mac supports VBA, but Windows COM components and Windows OLE DB providers do not simply carry over to macOS. A Windows ADO connection string is therefore not a portable Mac solution. If the workbook must run on both platforms, design and test a separate supported connection approach for each environment, or avoid relying on a Windows-only database provider.
Excel for the web does not execute VBA. If browser-based refresh or automation is required, investigate Power Query connectors available in the target environment, Office Scripts for supported workbook automation, or an approved database API or service. These are alternatives with different capabilities and authentication models, not a way to run the same VBA procedure in a browser.
Costs and maintenance to plan for
ADO itself is an interface, but a working connection can still have setup and ongoing costs. A database may require a license, a managed server, administrator time, network access, and backups. A driver may be free to download but still require installation approval and periodic updates. Access may be a practical file-based option for some small, controlled workflows, while a shared SQL Server database generally needs administration and appropriate licensing. Actual costs depend on the product, deployment, and organization; check current vendor terms rather than assuming that a driver or workbook makes the database free.
Plan for maintenance as well as initial setup. Database servers can move, passwords or permissions can change, tables can be redesigned, and providers can be updated. Keep connection settings understandable, avoid scattering credentials through several procedures, and document the workbook’s expected schema and owner. If many people depend on the workbook, have the database owner review its access method and failure handling before distributing it.
Frequently Asked Questions
Can Excel VBA connect directly to a database?
Yes. VBA can use ADO or another supported interface to connect through an installed provider or driver. The database must be reachable, and the user or service identity needs the required permissions.
Do I need to enable the ADO reference?
Not for the late-bound example above, which creates objects with CreateObject and declares them as Object. Early binding uses an ADO library reference and can provide clearer type information, but references and installed library versions must be managed on the computers that run the workbook.
Can I connect to an Access database with the SQL Server code?
No. Access uses a different provider and a file path, and the ACE provider must be available and compatible with Office. Use an Access-specific connection string and verify the installation before adapting the query.
Why does the macro say the provider cannot be found?
Usually the named provider is not installed, its installation is incompatible with the Office architecture, or the connection string names a provider that is unavailable. Confirm the exact provider required, its installation, and whether Office is 32-bit or 64-bit.
Can the same VBA database macro run in Excel for the web or on Mac?
Excel for the web does not run VBA. Excel for Mac can run VBA, but the Windows ADO and OLE DB setup shown here is not a portable Mac connection. Use a supported platform-specific approach or another approved tool when cross-platform or browser execution is required.
Related
- Excel Slicers for Multiple PivotTables: How to Connect
- Excel Named Ranges in Data Validation: How to Use Them
- Excel Unable to Remove Duplicates? Common Causes and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Array Example: Create, Fill, and Write DataA simple Excel VBA array example is to store values in a two-dimensional array, then write the array…
- VBA Excel Get Range Address: Examples and Common UsesIn Excel VBA, get a range’s address with its Address property: for example, Range("B2:D5").Address returns $B$2:$D$5 by default.…
- Excel VBA: Select a Range Using VariablesIn Excel VBA, use variables inside Cells(row, column) and pass the two cell references to Range to build…
- Excel VBA Array of Worksheets: Create and Use OneIn Excel VBA, you can work with several worksheets as a group by passing an array of sheet…
- Run Macro in Excel Not Working? Causes, Checks, and Fixes to TryIf a macro will not run in Excel, first check that you opened the file in the desktop…
- Excel VBA InputBox Syntax: Arguments and ExamplesIn Excel VBA, the basic syntax is InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context]).…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.