Excel VBA Connection to Access 2010

Question

I have a class that's handling my connection to an Access 2003 database. I would like to setup the same thing only for Access 07/10 .accdb files. Any help is appreciated! Thank you!

Here's a list of my references and a copy of the class object

References:

Microsoft Access 14.0 Object Library
Microsoft DAO 3.6 Object Library

ConnectionClass:

Option Explicit

Private Const DbFile = "\\server\folders\Report.mdb"
Dim OpenConn As DAO.Database
Dim ObjAccess As Object

Private Sub Class_Initialize()
    On Error Resume Next
    Set OpenConn = DAO.OpenDatabase(DbFile)
    If Err.Number = 3024 Then MsgBox "Check connection string in the VBA StaticClass object", vbOKOnly

    Set ObjAccess = CreateObject("Access.Application")
    ObjAccess.Visible = False
    ObjAccess.OpenCurrentDatabase (DbFile)
End Sub

Public Function runSQL(ByVal sql As String) As Recordset
    Set runSQL = OpenConn.OpenRecordset(sql)
End Function

Public Function runVolumeReport(ByVal inMacro As String)
    ObjAccess.DoCmd.RunMacro inMacro
End Function

Public Function closeResources()
    Set ObjAccess = Nothing
    OpenConn.Close
End Function

I would also Dim OpenConn As Object instead of DAO.Database. This allows for version independent code (but removes the cute popups you get when you type the . after OpenConn or DAO) — SeanC
– SeanC, Commented Aug 28, 2012 at 14:31
When I switch from .mdb to .accdb it gives a not recognized error. I will try to dig out the exact error later. — JKK
– JKK, Commented Aug 28, 2012 at 18:36
I had the file open, went ahead and flipped it to .accdb. It fails on the function runSQL, gives run time error 91, object variable or with block variable not set. The function runVolumeReport runs fine for kicking off the macro inside the database though. Just doesn't like the way I'm trying to pull a record set from Access when I switch it to the .accdb format — JKK
– JKK, Commented Aug 28, 2012 at 18:39

HansUp · Accepted Answer · 2012-08-29 15:39:08Z

There is an issue in Class_Initialize.

On Error Resume Next
Set OpenConn = DAO.OpenDatabase(DbFile)
If Err.Number = 3024 Then MsgBox "Check connection string in the VBA StaticClass object", vbOKOnly

Because of On Error Resume Next, any error other than 3024 ("Could not find file") will pass silently and OpenConn will not be set as you intend. Later when you attempt to use OpenConn, you will trigger another error. And, in a comment, you reported you do get another error with this line:

Set runSQL = OpenConn.OpenRecordset(sql)

Unfortunately, due to On Error Resume Next, we don't know why OpenDatabase failed leaving OpenConn unset. Since ObjAccess seems to work as an Access application object, you could try setting OpenConn to ObjAccess.CurrentDb.

Private Sub Class_Initialize()
    Set ObjAccess = CreateObject("Access.Application")
    ObjAccess.Visible = False
    ObjAccess.OpenCurrentDatabase DbFile
    Set OpenConn = ObjAccess.CurrentDb
End Sub

OTOH, you may be able to dispense with OpenConn entirely if you change your runSQL function like this ...

Public Function runSQL(ByVal sql As String) As Recordset
    'Set runSQL = OpenConn.OpenRecordset(sql) '
    Set runSQL = ObjAccess.CurrentDb.OpenRecordset(sql)
End Function

SeanC · Accepted Answer · 2012-08-29 14:15:18Z

0

One way to open a accdb (SQL Server) table is this:

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String

strSQL = "select SomeStuff from SomeTable"

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = strSQL ' you can put in the SQL directly, 
                         ' but I find the string easier to manipulate away from the .CommandText
Set rs = cmd.Execute

My References (Access 2010):
list of references
I think the critical one you would need to add would be the Microsoft ActiveX Data Objects X.X Library

answered Aug 29, 2012 at 14:15

SeanC

16k5 gold badges48 silver badges67 bronze badges

Comments

Peter · Accepted Answer · 2015-06-11 15:44:52Z

0

Imports System.Data.OleDb

Public Class Form1
    Dim strSQL As String
    Dim ds As New DataSet
    Dim strConnection As String
    Dim DBconnection As New OleDbConnection
    Dim oledbAdapter As New OleDbDataAdapter
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\Informatica\April - Juni\Acces\db-games.accdb"
        DBconnection = New OleDbConnection(strConnection)
        strSQL = "SELECT * from tbl_games"

        Try
            DBconnection.Open()
            oledbAdapter = New OleDbDataAdapter(strSQL, DBconnection)
            oledbAdapter.Fill(ds)
            DataGridView1.DataSource = ds.Tables(0)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        DBconnection.Close()
    End Sub
End Class

Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=P:\Informatica\Acces\db_Games.accdb;Persist Security Info=False") Dim cmd As New OleDbCommand

    con.Open()

    cmd.Connection = con

    cmd.CommandText = "INSERT INTO tbl_gerne(Omschrijving) VALUES('adventure')"
    cmd.ExecuteNonQuery()

    con.Close()

edited Jun 11, 2015 at 15:44

answered Jun 11, 2015 at 14:54

Peter

11 bronze badge

1 Comment

TZHX Over a year ago

Hi Peter, welcome to Stack Overflow. Please take the time to look at How to Answer and consider adding some supporting text to your post to explain what your code does.

Collectives™ on Stack Overflow

Excel VBA Connection to Access 2010

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related