Future of Access

[RonPaii]

I stated earlier that you could close the form and re-open - IF the connection has by that time re-established itself. Which is a possibility. The related question "DOES ACCESS STILL NEED TO WAIT" can only be answered by testing the connection to see if you have one. (See @RonPaii remark regarding WMI). If you have a connection but the previously open form has now gone bonkers, try to close it and find out. That comes back to whether the form is still around or whether it will respond to a DoCmd.Close of the form, or whatever other method you try.

The real headache in this question is whether Access itself has actually EXITED because of the lost connection between the FE and the linked tables in the BE. That is going to depend on factors such as the way the network was set up and whether the App's error handling (provided by the app's author) intercepted the error that would possibly be fatal to Access itself. I have seen cases where Access died because it lost contact with a linked BE table. Therefore, in answer to the "NEED TO WAIT" question, I have to reply, "Damned if I know. It depends on too many factors."



This presumes that Access is able to deal with that. Remember that Access is (a) single-threaded for code execution and (b) your code is not always in control if no relevant event has been recently triggered. This implies timer operations, and the problem there is that a complex timer routine is going to slow you down a lot. A MAIN program clearly could set up event interceptors to tell you what is going on, but you don't control all of the event vectors for MSACCESS.EXE, the MAIN program. That kind of hampers the detection process.

It's setup as a callback which raises an evert on my main form of the change. You don't need any timer and the main form would know how to shut down the other forms and/or the FE. BTW if you are seeing drops on a wired network, there is a hardware problem that needs to be fixed. I don't allow connections from WIFI or WAN.

'Class ClassWMI header'
Private oWMI_root_WMI  As SWbemServicesEx           ' Service Object "\root\WMI"
Private WithEvents oSinkDisconnect As SWbemSink     ' Disconnect Event object
Private WithEvents oSinkConnect As SWbemSink        ' Connect Event object
Public Event Changed(strChanged As String)          ' Raised when NIC changes
Private m_bMonitor As Boolean                       ' True if network monitor is active

'Setup callback called by class init'
Private Sub WMI_MontiorNetwork()
    On Error GoTo errWMI_MontiorNetwork
    Set oWMI_root_WMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\WMI")
    Set oSinkDisconnect = New SWbemSink
    Set oSinkConnect = New SWbemSink
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkDisconnect, "SELECT * FROM MSNdis_StatusMediaDisconnect"
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkConnect, "SELECT * FROM MSNdis_StatusMediaConnect"
    m_bMonitor = True
doneWMI_MontiorNetwork:
    Exit Sub
errWMI_MontiorNetwork:
    m_bMonitor = False
    'Me.WriteLog MODULENAME & ".WMI_MonitorNetwork", "Could not setup monitor. Error:( " & Err.Number & " ) " & Err.Description
    Resume doneWMI_MontiorNetwork
End Sub

'--------------------
' Shut-down network monitor on program close
Private Sub Class_Terminate()
    On Error Resume Next
    If Not oSinkDisconnect Is Nothing Then
        oSinkDisconnect.Cancel
        Set oSinkDisconnect = Nothing
    End If
    If Not oSinkConnect Is Nothing Then
        oSinkConnect.Cancel
        Set oSinkConnect = Nothing
    End If
    m_bMonitor = False
    If Not oWMI_root_WMI Is Nothing Then Set oWMI_root_WMI = Nothing
End Sub

'-----------------
' SWbemSink Event on disconnect of a NIC
Private Sub oSinkDisconnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                          ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has Disconnected")
End Sub

'-----------------
' SWbemSink Event on Connection of a NIC
'
Private Sub oSinkConnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                       ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has connected")
End Sub

'Main Form ---- Always open'
Private WithEvents oWMI As ClassWMI  ' Check Network status Handles ClassWMI events

'-------------------------------
'   Handle Changed event when WMI see a network change
'   Check status of network change on ClassWMI Changed event
'   Shut down application on WIFI, VPN or no Network
Private Sub oWMI_Changed(whatChanged As String)
         MsgBox whatChanged & vbCrLf & _
                "No network connections, found." & vbCrLf & vbCrLf & _
                "Closing the application.", _
                vbCritical, "No Active Connections"
End Sub

 

[The_Doc_Man]

What if an additional accdb could be setup to act as a network monitor that periodically checks the status of the connection and the status of Access app?

Windows security rules say that unless a formal mechanism is set up to allow task A to influence task B, that they will otherwise have nothing to do with each other. They will share no memory areas (like a common buffer area). They will not be able to reach into each other's memory even if they are both being run under the same username. They cannot take an action that would alter the other process's state (Run, Wait for I/O, Wait for memory, Wait for CPU availability, etc.). They will not be able to definitively know that the other process even exists at a given moment unless you start using all sorts of API tricks. Since this is potentially the same user, API tricks MIGHT work because of user permissions matching up.

That inter-task isolation has been the case ever since WinNT came out, and that isolation was built into WinNT because that feature was the only way that the U.S. Dept. of Defense would allow Microsoft to propose ANY technology based on Windows. All predecessors to WinNT would have been disqualified on the "task isolation" and "object re-use" rules. MS would have been disallowed to respond to the request for a proposal using Windows. Think about the economics of THAT little situation. The other two big security rules were in the "maybe" category but those two task rules were essentially a hard "go/no go" factor. You KNOW that MS fixed that ASAP.

I'm not saying you can't do anything about this problem, but you are now reaching the point where you might do better to just publish instructions for your users to manually close and restart Access when certain "network connection lost" messages crop up. We are approaching "severely convoluted programming" issues for a relatively simple problem to fix with a short manual procedure.

See also Pat Hartman's response, #180 of this thread.

 

[BlueSpruce]

Windows security rules say that unless a formal mechanism is set up to allow task A to influence task B, that they will otherwise have nothing to do with each other. They will share no memory areas (like a common buffer area). They will not be able to reach into each other's memory even if they are both being run under the same username. They cannot take an action that would alter the other process's state (Run, Wait for I/O, Wait for memory, Wait for CPU availability, etc.). They will not be able to definitively know that the other process even exists at a given moment unless you start using all sorts of API tricks. Since this is potentially the same user, API tricks MIGHT work because of user permissions matching up.

That inter-task isolation has been the case ever since WinNT came out, and that isolation was built into WinNT because that feature was the only way that the U.S. Dept. of Defense would allow Microsoft to propose ANY technology based on Windows. All predecessors to WinNT would have been disqualified on the "task isolation" and "object re-use" rules. MS would have been disallowed to respond to the request for a proposal using Windows. Think about the economics of THAT little situation. The other two big security rules were in the "maybe" category but those two task rules were essentially a hard "go/no go" factor. You KNOW that MS fixed that ASAP.

I'm not saying you can't do anything about this problem, but you are now reaching the point where you might do better to just publish instructions for your users to manually close and restart Access when certain "network connection lost" messages crop up. We are approaching "severely convoluted programming" issues for a relatively simple problem to fix with a short manual procedure.

See also Pat Hartman's response, #180 of this thread.

What does that have to do with one accdb opening up another accdb application and automating it?

You're telling me the accdb that opened the other accdb cannot find out if a form IsOpen, close it, or close the app, even though it created that instance? This has been done before. The accdb monitor can also interact with the windows filesystem, run shell commands, wmi api's, powerscripts, etc. to monitor the connections and the main app. Your telling me windows has no inter_process communications, shared memory, semaphores, like unix has?

 

[NauticalGent]

It's setup as a callback which raises an evert on my main form of the change. You don't need any timer and the main form would know how to shut down the other forms and/or the FE. BTW if you are seeing drops on a wired network, there is a hardware problem that needs to be fixed. I don't allow connections from WIFI or WAN.

'Class ClassWMI header'
Private oWMI_root_WMI  As SWbemServicesEx           ' Service Object "\root\WMI"
Private WithEvents oSinkDisconnect As SWbemSink     ' Disconnect Event object
Private WithEvents oSinkConnect As SWbemSink        ' Connect Event object
Public Event Changed(strChanged As String)          ' Raised when NIC changes
Private m_bMonitor As Boolean                       ' True if network monitor is active

'Setup callback called by class init'
Private Sub WMI_MontiorNetwork()
    On Error GoTo errWMI_MontiorNetwork
    Set oWMI_root_WMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strComputer & "\root\WMI")
    Set oSinkDisconnect = New SWbemSink
    Set oSinkConnect = New SWbemSink
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkDisconnect, "SELECT * FROM MSNdis_StatusMediaDisconnect"
    oWMI_root_WMI.ExecNotificationQueryAsync oSinkConnect, "SELECT * FROM MSNdis_StatusMediaConnect"
    m_bMonitor = True
doneWMI_MontiorNetwork:
    Exit Sub
errWMI_MontiorNetwork:
    m_bMonitor = False
    'Me.WriteLog MODULENAME & ".WMI_MonitorNetwork", "Could not setup monitor. Error:( " & Err.Number & " ) " & Err.Description
    Resume doneWMI_MontiorNetwork
End Sub

'--------------------
' Shut-down network monitor on program close
Private Sub Class_Terminate()
    On Error Resume Next
    If Not oSinkDisconnect Is Nothing Then
        oSinkDisconnect.Cancel
        Set oSinkDisconnect = Nothing
    End If
    If Not oSinkConnect Is Nothing Then
        oSinkConnect.Cancel
        Set oSinkConnect = Nothing
    End If
    m_bMonitor = False
    If Not oWMI_root_WMI Is Nothing Then Set oWMI_root_WMI = Nothing
End Sub

'-----------------
' SWbemSink Event on disconnect of a NIC
Private Sub oSinkDisconnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                          ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has Disconnected")
End Sub

'-----------------
' SWbemSink Event on Connection of a NIC
'
Private Sub oSinkConnect_OnObjectReady(ByVal objWbemObject As WbemScripting.ISWbemObject, _
                                       ByVal objWbemAsyncContext As WbemScripting.ISWbemNamedValueSet)
    On Error Resume Next
    Dim strNic As String
    Dim iNIC As Integer
    strNic = objWbemObject.Path_.RelPath
    ' Refill the NIC arrays
    'WMI_Fill_Nic
    ' Strip down to only name of NIC
    ' ex. MSNdis_StatusMediaDisconnect.InstanceName="Realtek PCIe GBE Family Controller"
    iNIC = InStr(1, strNic, "=")
    If iNIC > 1 Then
        strNic = Mid(strNic, iNIC + 2)
        strNic = left(strNic, Len(strNic) - 1)
    End If
    ' Let form know something changed
    RaiseEvent Changed(strNic & ", has connected")
End Sub

'Main Form ---- Always open'
Private WithEvents oWMI As ClassWMI  ' Check Network status Handles ClassWMI events

'-------------------------------
'   Handle Changed event when WMI see a network change
'   Check status of network change on ClassWMI Changed event
'   Shut down application on WIFI, VPN or no Network
Private Sub oWMI_Changed(whatChanged As String)
         MsgBox whatChanged & vbCrLf & _
                "No network connections, found." & vbCrLf & vbCrLf & _
                "Closing the application.", _
                vbCritical, "No Active Connections"
End Sub

Impressive!

 

[The_Doc_Man]

What if an additional accdb could be setup to act as a network monitor that periodically checks the status of the connection and the status of Access app?

What does that have to do with one accdb opening up another accdb application and automating it?

It will depend on how you opened the additional DB.

IF the DB is opened as an application object, then it is in a separate task and the communication between the two DBs is limited. This app object DB has a life of its own in the sense that if your opening DB exits without commanding the app object to also exit, you have a dangling task. The app object database is a "slave" to the opening DB in a way analogous but not identical to RDP. To my understanding of task structure, that app object's control channels (Keyboard, Video, Mouse or KVM) are a one-way street, master to slave only, because the separate tasks are subject to the "task isolation" rule. The slave cannot reach into the master because they are in separate chunks of physical memory. The slave cannot access the master's KVM channels because those are already open non-shared and not subject to external interference. The master CAN reach into the slave to trigger actions and change things, but not vice-versa. If you flip the script and have the monitoring task open the desired task, then the KVM of the desired task has the same limitations.

If the DB is opened as a DAO Database object, it is part of the same task as its opener, which means anything you did with it is single-threaded and I'm not sure but I don't think that such a DB opened in that particular way has active events. It doesn't have its own workspace and doesn't "know" that it is open. It doesn't have a separate KVM path... or in fact, ANY KVM path.

If the DB is opened by launching a hyper-link, it is very much like an app object as described above except the control path is different. In that case it would have the default KVM for any launched task that has a window. But it still counts as a separate task for isolation purposes. If you launched the DB from Windows explorer, it is the same as launching a hyperlink. (OR launching the hyperlink is the same as launching from an icon. Potato, patahtoe.)

Did I leave out a method of opening a DB? Offhand, I don't know of another way to open a DB.

 

[BlueSpruce]

It will depend on how you opened the additional DB.

IF the DB is opened as an application object, then it is in a separate task and the communication between the two DBs is limited. This app object DB has a life of its own in the sense that if your opening DB exits without commanding the app object to also exit, you have a dangling task. The app object database is a "slave" to the opening DB in a way analogous but not identical to RDP. To my understanding of task structure, that app object's control channels (Keyboard, Video, Mouse or KVM) are a one-way street, master to slave only, because the separate tasks are subject to the "task isolation" rule. The slave cannot reach into the master because they are in separate chunks of physical memory. The slave cannot access the master's KVM channels because those are already open non-shared and not subject to external interference. The master CAN reach into the slave to trigger actions and change things, but not vice-versa. If you flip the script and have the monitoring task open the desired task, then the KVM of the desired task has the same limitations.

If the DB is opened as a DAO Database object, it is part of the same task as its opener, which means anything you did with it is single-threaded and I'm not sure but I don't think that such a DB opened in that particular way has active events. It doesn't have its own workspace and doesn't "know" that it is open. It doesn't have a separate KVM path... or in fact, ANY KVM path.

If the DB is opened by launching a hyper-link, it is very much like an app object as described above except the control path is different. In that case it would have the default KVM for any launched task that has a window. But it still counts as a separate task for isolation purposes. If you launched the DB from Windows explorer, it is the same as launching a hyperlink. (OR launching the hyperlink is the same as launching from an icon. Potato, patahtoe.)

Did I leave out a method of opening a DB? Offhand, I don't know of another way to open a DB.

Sub SendStatusFromSlaveToMaster()
   Dim Connect    As Object
   Dim sDatabase1 As String
   Dim sDatabase2 As String

 
   sDatabase1 = "C:\Master.accdb"
   sDatabase2 = "C:\Slave.accdb"

   Set Connect = CreateObject("ADODB.Connection")
   With cn Connect
      .Provider = "Microsoft.ACE.OLEDB.14.0"
      .ConnectionString = "Data Source='" & sDatabase2 & "'"
      .Open
      .FuncSendStatus
      .Close
   End With

End Sub

 

[The_Doc_Man]

Since I don't use ADO, I'm not sure - but if it is at all like DAO, it will have the same issues as opening with DAO. You send the status as a file operation, not a DB operation. Where is .FuncSendStatus defined? This looks like air code since your WITH variable, cn, isn't defined.

I remain skeptical. I return to Pat's and my suggestion that at some point it is less convoluted to just instruct folks how to shut it down and start with a freshly loaded copy. And if that fails during its restart, the network wasn't ready yet.

 

[BlueSpruce]

Since I don't use ADO, I'm not sure - but if it is at all like DAO, it will have the same issues as opening with DAO. You send the status as a file operation, not a DB operation. Where is .FuncSendStatus defined? This looks like air code since your WITH variable, cn, isn't defined.

I remain skeptical. I return to Pat's and my suggestion that at some point it is less convoluted to just instruct folks how to shut it down and start with a freshly loaded copy. And if that fails during its restart, the network wasn't ready yet.

No pain, no gain. If we don't experiment, we don't progress! Linux supports IPC (Inter Process Communication), Shared Memory, and all governments use it, so why wouldn't they use Windows if it had those capabilities?

Sub SendStatusFromSlaveToMaster()
   Dim Connect    As Object
   Dim sDatabase1 As String
   Dim sDatabase2 As String

   sDatabase1 = "C:\Master.accdb"
   sDatabase2 = "C:\Slave.accdb"

   Set Connect = CreateObject("ADODB.Connection")
   With Connect
      .Provider = "Microsoft.ACE.OLEDB.14.0"
      .ConnectionString = "Data Source='" & sDatabase2 & "'"
      .Open
      .FuncSendStatus
      .Close
   End With

End Sub

 

[The_Doc_Man]

In the final analysis, all I can suggest is that if you want to try this, go ahead. The best that can happen is that you have found your way through the maze and win the prize. The worst that can happen is that it doesn't work. I've given you the background to understand the most probable network behavior of this process. I think you are striving for a really complex solution when a simpler solution is available. I remain skeptical but I can (and DO) wish you good luck in the attempt, if you choose to make it.

 

[BlueSpruce]

As I mentioned earlier, the Government of the State of Connecticut, USA is one of the biggest Access users. They have been migrating the data of 32,000+ siloed native backends to multi-tenant SQL Server Enterprise so their IT can better protect and manage the data. Other enterprise level Access users are adopting this model.

Here's a guide the CT State provides to all its users who want to create an Access app.

The guide doesn't really explain well how to design and build a normalised database. However, it does mention the Northwind sample app and hopefully newbies will study its normalised design and not build Excel like Access apps.

https://portal.ct.gov/-/media/ott/ott-office-365-online-training/6office365freebookaccess365.pdf

 

[isladogs]

Well I learned something skimming the first part of that PDF where I read that …

A dynaset is not to be confused with a dinosaur.

 

[BlueSpruce]

Well I learned something skimming the first part of that PDF where I read that …

I updated my original post with some comments about the guide.

 

[AHeyne]

(Currently?) the link doesn't work...

 

[BlueSpruce]

(Currently?) the link doesn't work...

It works for me and @isladogs.
Try this link:

https://portal.ct.gov/-/media/ott/ott-office-365-online-training/6office365freebookaccess365.pdf

Or search for "CT.gov Access 365 pdf"

Notice that the summary of that weblink is enough to deter users from exploring Access.

 

[BlueSpruce]

@GPGeorge, Does NW2 provide a guide that thoroughly explains how to properly design and build a normalised Access application?
Most newbies who explore Access are Excel users, and by habit, they will build denormalized Excel_like Access apps, unless we thoroughly educate them about normalized database design. This is sorely needed, as I have not seen any adequate content about the subject matter.

 

[GPGeorge]

@GPGeorge, Does NW2 provide a guide that thoroughly explains how to properly design and build a normalised Access application?
Most newbies who explore Access are Excel users, and by habit, they will build denormalized Excel_like Access apps, unless we thoroughly educate them about normalized database design. This is sorely needed, as I have not seen any adequate content about the subject matter.

A guide? Not a document as such. That's actually beyond the scope of the charter for Microsoft templates in general.

We went to some trouble to provide a consistent Relationship Diagram for the Developer version because otherwise it's generated in the muddled state typical of newly created databases.

Over the years, I've seen dozens of articles that attempt to explain database Normalization, Normal Forms, table design, etc. A typical web search should turn up a boatload of them. You can even find very good YouTube videos on the subject.

I doubt there would be much incremental value to trying to limit one to Access applications in particular, though. All relational databases are built on the same principles, regardless of the database itself. A Primary Key is a Primary Key, in other words. So building a normalized Access database application differs only in the degree of complexity starters will implement.

My own original attempt at explaining it was in my first book, back in 2004. I did a marginal job of it, unfortunately for my pride, but I felt good about it at the time.

It doesn't matter that much, though, in a practical sense, with regard to Excel users. They don't know what they don't know when the decide to tackle Access. So, unless they are brilliant, or just unusually lucky, they blunder on until they realize what they've created doesn't work. At that point, many of them begin their search for help, eventually stumbling into someone who shakes a finger in their face and shouts "Normalize" at them.

 

[BlueSpruce]

A guide? Not a document as such. That's actually beyond the scope of the charter for Microsoft templates in general.

We went to some trouble to provide a consistent Relationship Diagram for the Developer version because otherwise it's generated in the muddled state typical of newly created databases.

Over the years, I've seen dozens of articles that attempt to explain database Normalization, Normal Forms, table design, etc. A typical web search should turn up a boatload of them. You can even find very good YouTube videos on the subject.

I doubt there would be much incremental value to trying to limit one to Access applications in particular, though. All relational databases are built on the same principles, regardless of the database itself. A Primary Key is a Primary Key, in other words. So building a normalized Access database application differs only in the degree of complexity starters will implement.

My own original attempt at explaining it was in my first book, back in 2004. I did a marginal job of it, unfortunately for my pride, but I felt good about it at the time.

It doesn't matter that much, though, in a practical sense, with regard to Excel users. They don't know what they don't know when the decide to tackle Access. So, unless they are brilliant, or just unusually lucky, they blunder on until they realize what they've created doesn't work. At that point, many of them begin their search for help, eventually stumbling into someone who shakes a finger in their face and shouts "Normalize" at them.

The analogy of the cardboard box in your Data Modeling 101 (2004) book chapter is great for beginners, but I think now most people who want to learn how to develop an Acess app, by example, are going to reference your NW2 template. Therefore:

I feel it would be worthwhile to provide a new guide that shows Excel users how to properly design and build a normalised Access application. The guide could show examples of NW2 data in flat Excel sheets and how that data should be organized in Access tables, and explanations in layman terms of the rules for 3rd Normal Form, which is sufficient enough for properly building Access apps. This topic really doesn't have to be that long to guide users in the right direction.

 

[AHeyne]

It works for me and @isladogs.
Try this link:

https://portal.ct.gov/-/media/ott/ott-office-365-online-training/6office365freebookaccess365.pdf

Or search for "CT.gov Access 365 pdf"

Notice that the summary of that weblink is enough to deter users from exploring Access.

View attachment 121546

Ok, the problem seems to belong to my location:

The Amazon CloudFront distribution is configured to block access from your country.

But it's no problem. It's not that important.

 

[GPGeorge]

The analogy of the cardboard box in your Data Modeling 101 (2004) book chapter is great for beginners, but I think now most people who want to learn how to develop an Acess app, by example, are going to reference your NW2 template. Therefore:

I feel it would be worthwhile to provide a new guide that shows Excel users how to properly design and build a normalised Access application. The guide could show examples of NW2 data in flat Excel sheets and how that data should be organized in Access tables, and explanations in layman terms of the rules for 3rd Normal Form, which is sufficient enough for properly building Access apps. This topic really doesn't have to be that long to guide users in the right direction.

As I said, there have been many such attempts to provide guidelines for designing and building normalized relational databases, some brilliant, some less so. I bet a search right here at AWF would turn up more than one.

The problem, as I see it, isn't a lack of written guides. The problem is that most people just starting out have no idea they even need such a guide before they begin. They jump in, importing a spreadsheet and hacking away. Only when they fail to achieve something usable do they start looking around for help. If they're lucky they find a discussion on Relational Database design and/or Database Normalization and take it seriously. We've all seen those cases, however, when they refuse to give up what they've already done just because they're informed there is a better way.

I remember saying many years ago, and only partly in jest, that in place of a license, users should have to pass a short test on Database Normalization before being permitted to install Access on their computers.

All of that said, if you have time, perhaps you'd be able take up the task of writing a new guide for Access relational design. It certainly couldn't hurt.

 

[BlueSpruce]

As I said, there have been many such attempts to provide guidelines for designing and building normalized relational databases, some brilliant, some less so. I bet a search right here at AWF would turn up more than one.

The problem, as I see it, isn't a lack of written guides. The problem is that most people just starting out have no idea they even need such a guide before they begin. They jump in, importing a spreadsheet and hacking away. Only when they fail to achieve something usable do they start looking around for help. If they're lucky they find a discussion on Relational Database design and/or Database Normalization and take it seriously. We've all seen those cases, however, when they refuse to give up what they've already done just because they're informed there is a better way.

I remember saying many years ago, and only partly in jest, that in place of a license, users should have to pass a short test on Database Normalization before being permitted to install Access on their computers.

All of that said, if you have time, perhaps you'd be able take up the task of writing a new guide for Access relational design. It certainly couldn't hurt.

So therefore, it should be built into Access itself to ensure newbies read and follow the rules. That can be disabled later on by changing the help level, just like when Word 2000 use to have help levels 1,2, and 3.

After all, wasn't Access originally built as a user-friendly database tool?

Perhaps you could suggest this idea to the MS Access Team Manager?

If MS where to build in that feature, there would be less Access users needing their apps to be rescued by experienced developers, a double-edged sword. Which scenario do you prefer?