ACCESS FEED: RE: [MS_AccessPros] VBA to Macro?

Selasa, 11 Maret 2014

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 (19)

Visit Your Group