I think I'm missing something here (other than all significant content from previous emails in this thread).
Why would you ever use Excel as an interface to Access tables for any reason other than reporting? I use Excel extensively when creating pivot tables, graphs, and data dumps. I would never consider using Excel as a read-write interface to a relational database when Access forms, subforms, queries, vba, etc make it so much easier.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: jpjones23@centurylink.net
Date: Tue, 11 Mar 2014 19:37:17 -0400
Subject: Re: [MS_AccessPros] VBA to Macro?
Why would you ever use Excel as an interface to Access tables for any reason other than reporting? I use Excel extensively when creating pivot tables, graphs, and data dumps. I would never consider using Excel as a read-write interface to a relational database when Access forms, subforms, queries, vba, etc make it so much easier.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: jpjones23@centurylink.net
Date: Tue, 11 Mar 2014 19:37:17 -0400
Subject: Re: [MS_AccessPros] VBA to Macro?
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 (16) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar