Toukey,
Still might try Excel. There's plenty of VBA code examples available to get Excel to talk to Access. Once the link is established, some things like the following could help.
1). Connect to Access from Excel
'I always place the database name and its full path in a cell that the user controls for when it gets moved. It's path and name is in the strDBLoc variable. The dbOpenAttrib would be a password if needed.
Sub Access2003_Access2007_ConnectionSyntax()
' open the database
If Access2003 Then ' This is for Access 2003
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDBLoc & ";" '& "Jet OLEDB:Database Password=" & dbOpenAttrib
' Note the ".Jet" and "4.0"
Else
' This is for Access 2007
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLoc & ";" '& "Jet OLEDB:Database Password=" & dbOpenAttrib
' Note the ".ACE." and "12.0"
End If
End Sub
2). Retrieve Access Data
' This will execute a query put together in this procedure to pull down Account data for a combobox on the form.
On Error GoTo HandleError
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source") = strDBLoc
.Open
End With
' Get Department data with following query
strSQLQuery = "SELECT DISTINCT Purpose & '-' & Mid(AccountNo,8) AS PurposeObject FROM Positions ORDER BY Purpose & '-' & Mid(AccountNo,8); "
Set rs = New ADODB.Recordset
With rs
.Open strSQLQuery, cn, , , adCmdText
' WriteToWrksheet rs, TargetRange ' write data from the recordset to the worksheet
End With
cboAccount.Clear
If rs.EOF Then
' MsgBox "There are no queries in the database."
Exit Sub
Else
Do Until rs.EOF
cboAccount.AddItem rs.Fields("PurposeObject")
rs.MoveNext
Loop
End If
cboAccount.ListIndex = 1
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub
3). List tables.
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
If obj.IsLoaded = True Then
' Print name of obj.
Debug.Print obj.Name
End If
Debug.Print obj.Name
Next obj
4). List table fields
Private Sub cmdListFields_Click()
Dim dbs As Database
Dim rst As Recordset
Dim strDBName As String
Dim strTable As String
Dim fld As Field
strTable = "Categories"
strDBName = "D:\Documents\Northwind.mdb"
Set dbs = OpenDatabase(strDBName)
Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
With rst
.MoveLast
For Each fld In .Fields
Debug.Print fld.Name & " value: " & fld.Value
Next fld
.Close
End With
End Sub
And so on. The same sorts of things can be done with queries.
Jeff
Still might try Excel. There's plenty of VBA code examples available to get Excel to talk to Access. Once the link is established, some things like the following could help.
1). Connect to Access from Excel
'I always place the database name and its full path in a cell that the user controls for when it gets moved. It's path and name is in the strDBLoc variable. The dbOpenAttrib would be a password if needed.
Sub Access2003_Access2007_ConnectionSyntax()
' open the database
If Access2003 Then ' This is for Access 2003
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDBLoc & ";" '& "Jet OLEDB:Database Password=" & dbOpenAttrib
' Note the ".Jet" and "4.0"
Else
' This is for Access 2007
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strDBLoc & ";" '& "Jet OLEDB:Database Password=" & dbOpenAttrib
' Note the ".ACE." and "12.0"
End If
End Sub
2). Retrieve Access Data
' This will execute a query put together in this procedure to pull down Account data for a combobox on the form.
On Error GoTo HandleError
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source") = strDBLoc
.Open
End With
' Get Department data with following query
strSQLQuery = "SELECT DISTINCT Purpose & '-' & Mid(AccountNo,8) AS PurposeObject FROM Positions ORDER BY Purpose & '-' & Mid(AccountNo,8); "
Set rs = New ADODB.Recordset
With rs
.Open strSQLQuery, cn, , , adCmdText
' WriteToWrksheet rs, TargetRange ' write data from the recordset to the worksheet
End With
cboAccount.Clear
If rs.EOF Then
' MsgBox "There are no queries in the database."
Exit Sub
Else
Do Until rs.EOF
cboAccount.AddItem rs.Fields("PurposeObject")
rs.MoveNext
Loop
End If
cboAccount.ListIndex = 1
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
Exit Sub
3). List tables.
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
If obj.IsLoaded = True Then
' Print name of obj.
Debug.Print obj.Name
End If
Debug.Print obj.Name
Next obj
4). List table fields
Private Sub cmdListFields_Click()
Dim dbs As Database
Dim rst As Recordset
Dim strDBName As String
Dim strTable As String
Dim fld As Field
strTable = "Categories"
strDBName = "D:\Documents\Northwind.mdb"
Set dbs = OpenDatabase(strDBName)
Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
With rst
.MoveLast
For Each fld In .Fields
Debug.Print fld.Name & " value: " & fld.Value
Next fld
.Close
End With
End Sub
And so on. The same sorts of things can be done with queries.
Jeff
From: "toukey1" <no_reply@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, March 11, 2014 5:57:07 PM
Subject: Re: [MS_AccessPros] VBA to Macro?
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, March 11, 2014 5:57:07 PM
Subject: Re: [MS_AccessPros] VBA to Macro?
Thanks for your info Jeff
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (14) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar