I generally say that I'm an MS Office developer. I do a lot of Access but also do a lot of apps that use combinations of Office apps such as Excel / Word / Outlook or Word / Excel / PowerPoint or MS Project / Text File or Word / Zip. I've developed many stand alone Access databases, Excel tools, PowerPoint tools, MS Project tools, and Word tools.
It's the data, the environment, and the user community that dictates whatever solution I devise. It always nervouses me when I hear someone make a firm statement to the effect that there's "only one way" to satisfy a set of requirements. This is frequently the same person who proudly professes to have 20-years experience when, in actuality, said individual really has 1 year 20 times. LOL
Thank you again,
Jeff
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Tuesday, March 11, 2014 8:51:11 PM
Subject: RE: [MS_AccessPros] VBA to Macro?
Jeff
I see where you are coming from (if I can use that 70's expression). Excel and Access can work together and produce some great apps. The thing is that Access developers tend to ignore Excel because they think at a relational database level, not realizing that excel is a great analytical tool. Access was never meant to be an analytical tool.
Thanks for broadening our knowledge base.
Regards,
Bill
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of jpjones23@centurylink.net
Sent: Tuesday, March 11, 2014 5:35 PM
To: MS Access Professionals
Subject: Re: [MS_AccessPros] VBA to Macro?
I guess you've never run into the same sorts of clients or requirements. There are numerous reasons for having an Excel / Access partnership.
Excel is NOT a platform to store database data, especially one to many or many to many data relationships; Access is. However, Excel does a FAR better job allowing one to analyze the data so it makes sense to store data in Access while having Excel do the ongoing analysis.
There are times when a department has responsibility for the integrity of the data i the database but other departments need to be able to get the data. Without having Access licenses on every desktop and without needing to fiddle with ongoing access rights to the database, Excel is a great way to allow a department to retrieve needed data without exposing the database to unauthorized personnel.
Excel allows much greater flexibility to do "what if" scenarios on data than Access does.
Jeff
From: "Duane Hookom" <duanehookom@hotmail.com>
To: "Access Professionals Yahoo Group" <ms_access_professionals@yahoogroups.com>
Sent: Tuesday, March 11, 2014 8:13:30 PM
Subject: RE: [MS_AccessPros] VBA to Macro?
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?
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
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?
Thanks for your info Jeff
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (20) |
Tidak ada komentar:
Posting Komentar