INU Login Sign up
Login Or Sign up

Answers for "vba query access database"

VBA
1

vba query access database

'VBA function to query an Access database. Returns and ADO recordset:

Function QuerySQL(sql$, dbFile$)
    Dim cnx
    Set cnx = CreateObject("ADODB.connection")
    cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFile
    Set QuerySQL = CreateObject("ADODB.Recordset")
    With QuerySQL
        .CursorLocation = 3 'adUseClient
        .CursorType = 1     'adOpenKeyset
        .Open sql, cnx
    End With
End Function
    
'---------------------------------------------------------------------
    
MsgBox QuerySQL("SELECT * from Titanic", "C:\Ships.accdb").RecordCount
Posted by: Guest on April-25-2020
2

excel vba accessing sql database

'If the database is MS Access, the most performant way to run SQL
'queries from Excel VBA is by using DAO instead of ADO:

Function QuerySQL_DAO(sql$, dbFile$)
    Const DAO = "DAO.DBEngine.120"
    Set QuerySQL_DAO = CreateObject(DAO).OpenDatabase(dbFile).OpenRecordset(sql)
    QuerySQL_DAO.MoveLast
    QuerySQL_DAO.MoveFirst
End Function
   
'---------------------------------------------------------------------
    
MsgBox QuerySQL_DAO("SELECT * from Titanic", "C:\Ships.accdb").RecordCount         


  

'For all other databases, use ADO:

Function QuerySQL_ADO(sql$, dbFile$)
    Dim cnx
    Set cnx = CreateObject("ADODB.Connection")
    cnx.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbFile
    Set QuerySQL_ADO = CreateObject("ADODB.Recordset")
    With QuerySQL_ADO
        .CursorLocation = 3 'adUseClient
        .CursorType = 1     'adOpenKeyset
        .Open sql, cnx
    End With
End Function
    
'---------------------------------------------------------------------
    
MsgBox QuerySQL("SELECT * from Titanic", "C:\Ships.accdb").RecordCount
Posted by: Guest on May-02-2020

Code answers related to "vba query access database"

Code answers related to "VBA"

Browse Popular Code Answers by Language

Python
Javascript
Whatever
Shell/Bash
CSS
Html
PHP
SQL
Java
C#

Popular Programming Languages

Abap ActionScript Assembly BASIC C C# C++ Clojure Cobol CSS Dart Delphi Elixir Erlang F# Fortran Go Groovy Haskell Html Java Javascript Julia Kotlin Lisp Lua Matlab Objective-C Pascal Perl PHP Prolog Python R Ruby Rust Scala Scheme Shell/Bash SQL Swift TypeScript VBA Whatever

Advertisements

290x500

Company

Compilers

Help

Connect with us

footer logo

Copyright © 2021 Codeinu