Staring MS Access from VB ??

Hi folks,

Can anyone give me some idea how to start up Microsoft Access from within VB
?
I have a MDB which needs to be opened periodically and I was thinking that I
could create an VB.exe and just fire it up from there !

Any info would be appreciated..

Thanks
Davey

 

Well, the first thing you'll want to do is add a reference to the Microsoft
ActiveX Data Objects Library. Next, you'll want to create a connection
object similar to the following example:

Public oConnectAC As ADODB.Connection

'set global MDB database connection
Set oConnectAC = New ADODB.Connection

Then, actually open the database [MDB] file to access such as in the
following:

oConnectAC.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & <MDB
Filename.MDB> & ";"

Next, to actually access the records of the database, you'll need a
recordset similar to the following example:

Public rsParts As ADODB.Recordset

Set rsParts = New ADODB.Recordset

Then a SQL string to access a specific set of records like:

Dim Parts As String

Parts = "Select * From PartsTable"

Now, you need to actually open the recordset [known as returning a set of
records] like the following

rsParts.Open Parts, oConnectAC, adOpenKeyset, adLockOptimistic

If you want to see if your query actually returned a set of records, the
following construct will provide that feedback:

With rsParts
Do While Not .EOF
Debug.Print !PartNumber
.MoveNext 'move to the next record
Loop
End With

Here is all the code wrapped up into a working form. First, create a
standard .bas module and add this code to it:

Public oConnectAC As ADODB.Connection
Public rsParts As ADODB.Recordset

Then, add the following code to your Form_Load event

Private Sub Form_Load()
Dim Parts As String

'set global MDB database connection
Set oConnectAC = New ADODB.Connection

oConnectAC.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
"C:\db1.MDB" & ";"

Set rsParts = New ADODB.Recordset

Parts = "Select * From PartsTable"

rsParts.Open Parts, oConnectAC, adOpenKeyset, adLockOptimistic

With rsParts
Do While Not .EOF
Debug.Print !PartNumber
.MoveNext 'move to the next record
Loop
End With
End Sub

That should give you enough to get going. Hope this helps.

Joe

 

Hi Davey

Maybe this one helps:

Code:

Sub StartAccess()

Dim AccObj As Object
Dim OffVer As String
Dim AccTit As String
Dim DbsPth As String

OffVer = "10"                          'set to used office version
DbsPth = "x:\Databases\MyDatabase.mdb" 'set your database

On Error Resume Next
Set AccObj = GetObject(, "Access.Application." & OffVer)
If Err <> 0 Then
Err.Clear
Set AccObj = CreateObject("Access.Application." & OffVer)
If Err <> 0 Then
Call MsgBox("Description: " & Err.Description & _
vbCrLf & "Number: " & Err.Number, _
vbExclamation + vbOKOnly, "Error message")
Err.Clear
Else
With AccObj
.Visible = True
.OpenCurrentDatabase DbsPth, False
.UserControl = True
End With
End If
Else
AccTit = AccObj.CurrentDb.Properties("AppTitle")
AppActivate AccTit, True
If Err <> 0 Then
Err.Clear
AppActivate "Microsoft Access", True
End If
End If

Set AccObj = Nothing

End Sub

Cheers

 

On the other hand, if you actually want to "start Access" from VB or VBA,
you will want to work with an Access Application object. It looks like
this.

' get or start an Access Application Object
Public Sub GetAccessApp()
Dim oAccess As Object ' MS Access application object, late
bound

Set oAccess = Nothing ' set object to known value
' Try to get an existing open Access appl.
' GetObject([pathname] [, class])
' If the pathname argument is omitted, GetObject returns a currently
active object of the specified type.
' If no object of the specified type exists, an error occurs.
' (ErrNumber = 429, "ActiveX component can't create object.")
On Error Resume Next
Set oAccess = GetObject(, "Access.Application")

If oAccess Is Nothing Then ' Access was not open
Err.Clear
' CreateObject(class,[servername])
' If servername is an empty string (""), the local machine is used.
Set oAccess = CreateObject("Access.Application", "")
oAccess .OpenCurrentDatabase <path to your .MDB>
End If
On Error GoTo 0

' use the Access Application objects, properties, & methods (like DoCmd)
' to interact with your Access objects.

Set oAccess = Nothing ' cleanup the Access Application
object
End Sub