Solved Previously working code now prompting for Macro (1 Viewer)

[ThatTransportationPerson]

Hello!

Let me start by stating that I'm pretty much a novice when it comes to Access, so please let me know if I can explain things more clearly or use different verbiage. I have an Access database that was created by someone else years ago. Its purpose was to take Access data from a piece of software (let's call it "P") and import it into an .mdb where it could be used for GIS applications. When we upgraded the "P" software approx. 3 years ago, it no longer provided Access data and therefore this Access database was rendered useless. However, I recently discovered a way to convert the "P" data back into Access and I am attempting to once again use this Access database.

Unfortunately, when I push the button to execute the VBA (to push the data to the .mdb), Access crashes. No errors. If I open Visual Basic and attempt to run the VBA, it prompts me for a Macro. Here's the code:

Private Sub UpdateFromP()
'pull data from linked P tables and update linked GIS geodatabase tables

Dim strSourceFields As String
Dim strDestFields As String
Dim rsPCIAGE As ADODB.Recordset
Dim rs As ADODB.Recordset

Set rsPCIAGE = New ADODB.Recordset
Set rs = New ADODB.Recordset

rsPCIAGE.Open "SELECT * FROM AAllAirportsAlbers ORDER BY ID;", CurrentProject.Connection, adOpenStatic, adLockPessimistic

rs.Open "SELECT Sections.ID, BranchID, [Name], Use, InspPCI, PCISource, Area, ConstDate, InspDate, Round(DateDiff('m',[ConstDate],[InspDate])/12,0) AS InspAge " & _
"FROM (SELECT DISTINCT zUser_InspectionsAllYears.ID, Condition AS InspPCI, PCISource, Date AS InspDate, Area " & _
"FROM (SELECT ID, Max([Date]) AS LastInspDate FROM zUser_InspectionsAllYears GROUP BY ID) AS Query1 INNER JOIN zUser_InspectionsAllYears ON Query1.ID=zUser_InspectionsAllYears.ID " & _
"WHERE zUser_InspectionsAllYears.Date=[LastInspDate]) AS LastInsp RIGHT JOIN ((SELECT DISTINCT zUser_MajorMRAllYears.ID, zUser_MajorMRAllYears.Date AS ConstDate " & _
"FROM (SELECT ID, Max(Date) As LastInspDate FROM zUser_InspectionsAllYears GROUP BY ID) AS Q1 RIGHT JOIN ((SELECT ID, Max(Date) AS LastConstDate FROM zUser_MajorMRAllYears " & _
"GROUP BY zUser_MajorMRAllYears.ID) AS Q2 RIGHT JOIN zUser_MajorMRAllYears ON Q2.ID = zUser_MajorMRAllYears.ID) ON Q1.ID = zUser_MajorMRAllYears.ID " & _
"WHERE (((zUser_MajorMRAllYears.Date) = [LastConstDate] And (zUser_MajorMRAllYears.Date) <= [LastInspDate]))) AS LastConst RIGHT JOIN (SELECT [Uzukzxcttnsspyqmtdko] & [SectionID] AS ID, BranchID, Name, Use " & _
"FROM [Network-Extension] RIGHT JOIN (Branch RIGHT JOIN [Section] ON Branch.[_BUNIQUEID] = [Section].[_BUNIQUEID]) ON [Network-Extension].[_NUNIQUEID] = Branch.[_NUNIQUEID]) AS Sections ON LastConst.ID = Sections.ID) ON LastInsp.ID = Sections.ID " & _
"ORDER BY Sections.ID;", CurrentProject.Connection, adOpenStatic, adLockPessimistic

While Not rsPCIAGE.EOF
    rs.Find "ID='" & rsPCIAGE!ID & "'"
    If Not rs.EOF Then
        rsPCIAGE!Branch = rs!BranchID
        rsPCIAGE!BranchName = rs![Name]
        rsPCIAGE!PCI = rs!InspPCI
        rsPCIAGE!Age = rs!InspAge
        rsPCIAGE!Area = rs!Area
        rsPCIAGE!Use = rs!Use
        rsPCIAGE!LastInspec = rs!InspDate
        rsPCIAGE!LastConst = rs!ConstDate
    End If
    rs.MoveFirst
    rsPCIAGE.MoveNext
Wend
rsPCIAGE.MoveFirst 'to commit final record edits
rsPCIAGE.Close
rs.Close

CurrentDb.Execute "UPDATE AAllAirportsAlbers Set AdjPCI = PCI - FormatNumber(DateDiff('m', nz(LastInspec,0), Date()) / 12, 0) * 3, AdjAge = Age + FormatNumber(DateDiff('m', nz(LastInspec,0), Date()) / 12, 0)"
CurrentDb.Execute "UPDATE AAllAirportsAlbers Set AdjPCI = IIf(AdjPCI < 0, 0, AdjPCI)"
CurrentDb.Execute "UPDATE AAllAirportsAlbers SET RepYear = RepairYear([PCI],IIf([LastInspec]>[LastConst],DatePart('yyyy',[LastInspec]),DatePart('yyyy',[LastConst])),IIf([Use]='Runway', 70, 60))"
CurrentDb.Execute "DELETE FROM ALLBranchPCIAge"
CurrentDb.Execute "INSERT INTO AllBranchPCIAge (FAAID, Branch, Use, SumArea, AveragePCI, AverageAge, WeightedPCI, WeightedAge )" & _
    "SELECT Totals.FAAID, Totals.Branch, Totals.Use, Sum(Totals.Area) AS BranchArea, Round(Avg([PCI]),0) AS BranchPCI, Round(Avg([Age]),0) AS BranchAge, " & _
    "Round(Sum([PCI]*[Area])/Sum([Area]),0) AS WPCI, Round(Sum([Age]*[Area])/Sum([Area]),0) AS WAge " & _
    "FROM (SELECT FAAID, Branch, Use, PCI, Age, Area FROM AAllAirportsAlbers GROUP BY FAAID, Branch, Use, PCI, Age, Area) AS Totals " & _
    "GROUP BY Totals.FAAID, Totals.Branch, Totals.Use;"

strSourceFields = "ID, [Name], Condition, [_Latest], PCISource, Use, [Date], Area, FAAID"
strDestFields = "SectionID, BranchName, Condition, Latest, Source, Use, InspectionDate, Area, FAAID"

CurrentDb.Execute "DELETE FROM PaverData_InspectionsAllYears"
CurrentDb.Execute "DELETE FROM PaverData_MajorMRAllYears"
CurrentDb.Execute "INSERT INTO PaverData_InspectionsAllYears(" & strDestFields & ") SELECT " & strSourceFields & " FROM zUser_InspectionsAllYears;"
CurrentDb.Execute "INSERT INTO PaverData_MajorMRAllYears(SectionID, BranchName, Use, ConstDate, FAAID) SELECT ID, [Name], Use, [Date], FAAID FROM zUser_MajorMRAllYears;"
CalcAges

End Sub

Private Sub CalcAges()

Dim cn As ADODB.Connection
Dim rsConst As ADODB.Recordset
Dim rsInsp As ADODB.Recordset
Dim strNetwork As String
Dim strSection As String
Dim dblAge As Double
Set cn = CurrentProject.Connection
Set rsConst = New ADODB.Recordset
Set rsInsp = New ADODB.Recordset

rsInsp.Open "SELECT FAAID, SectionID, InspectionDate, Age FROM PaverData_InspectionsAllYears " & _
"ORDER BY FAAID, SectionID, InspectionDate;", cn, adOpenStatic, adLockPessimistic
While Not rsInsp.EOF
    If strNetwork <> rsInsp!FAAID Or strSection <> rsInsp!SectionID Then
        If rsConst.State = 1 Then
            rsConst.Close
        End If
        rsConst.Open "SELECT FAAID, SectionID, ConstDate " & _
        "FROM PaverData_MajorMRAllYears WHERE FAAID='" & rsInsp!FAAID & "' AND SectionID='" & rsInsp!SectionID & "' " & _
        "ORDER BY FAAID, SectionID, ConstDate;", cn, adOpenStatic, adLockPessimistic
        strNetwork = rsConst!FAAID
        strSection = rsConst!SectionID
    End If
    While Not rsConst.EOF
        If rsConst!ConstDate <= rsInsp!InspectionDate Then
            dblAge = DateDiff("m", rsConst!ConstDate, rsInsp!InspectionDate) / 12
            If DatePart("yyyy", rsConst!ConstDate) = DatePart("yyyy", rsInsp!InspectionDate) And dblAge >= 1 Then
                dblAge = dblAge - 1
            End If
        End If
        rsConst.MoveNext
    Wend
    rsInsp!Age = FormatNumber(IIf(dblAge < 1, 0, dblAge), 0)
    rsInsp.Update
    rsInsp.MoveNext
    rsConst.MoveFirst
Wend
rsInsp.Close

End Sub

Does anyone have an idea of why this might be happening?

 

[cheekybuddha]

If I open Visual Basic and attempt to run the VBA, it prompts me for a Macro

How are you doing this? (The 'attempt to run the VBA' part)

 

[ThatTransportationPerson]

How are you doing this? (The 'attempt to run the VBA' part)

Hi Cheekybuddha, I place my curser in front of "Private Sub" and click Run.

 

[theDBguy]

Hi. Welcome to AWF!

Can you post a screenshot of the error message?

 

[cheekybuddha]

Hi Cheekybuddha, I place my curser in front of "Private Sub" and click Run.

OK,
first, change the Private to Public:

Public Sub UpdateFromP()

Then, try (with your form open) typing in the Immediate Window (Ctrl+G):
Forms.NameOfYourForm.UpdateFromP
and hit Enter.

Then report the error as DBG requests

 

[ThatTransportationPerson]

OK,
first, change the Private to Public:

Public Sub UpdateFromP()

Then, try (with your form open) typing in the Immediate Window (Ctrl+G):
Forms.NameOfYourForm.UpdateFromP
and hit Enter.

Then report the error as DBG requests

Did as you requested and got the same result as when I click the button on the form. Access crashed without any errors.

 

[cheekybuddha]

Does the code compile?

Make sure you have Option Explicit declared at the top of every code module.

Then, in the VBA editor menu, Debug -> Compile

 

[ThatTransportationPerson]

I checked and Option Explicit is declared for each module. I clicked Compile and I believe it worked? I've never compiled in Access before. Everything froze for a few seconds and then went back to normal. The Compile option is now greyed out.

 

[cheekybuddha]

Do you have linked tables? If so, is the backend available?

Add a breakpoint to this line:

Set rsPCIAGE = New ADODB.Recordset

(Click in the grey margin to the left of the code - a red dot should appear).

Then run the sub again (as per Post #5)

The code should stop on that line (it will be highlighted in yellow).

Then press F8 to advance the code line by line.

Try and memorise what line you are on when Access crashes and report back.

 

[ThatTransportationPerson]

Do you have linked tables? If so, is the backend available?

Add a breakpoint to this line:

Set rsPCIAGE = New ADODB.Recordset

(Click in the grey margin to the left of the code - a red dot should appear).

Then run the sub again (as per Post #5)

The code should stop on that line (it will be highlighted in yellow).

Then press F8 to advance the code line by line.

Try and memorise what line you are on when Access crashes and report back.

This does have linked tables, it is linking to an .mdb that I can open.

While Not rsPCIAGE.EOF

This is the line that it crashes on.

 

[cheekybuddha]

Interesting!

Does it happen on the first pass, or do you manage to loop some records before the crash?

Check the references: VBA editor -> Tools -> References

First see if any are marked as 'Missing'

Then let us know which version of Microsoft ActiveX Data Objects is checked.

I don't see why this should be a problem, but at the same time I don't see the advantage of using ADODB recordsets is here, especially as the code mixes with DAO methods too.

 

[ThatTransportationPerson]

It happens on the first pass. I was wondering if it was a reference issue (they seem to happen quite often), and I checked earlier, but nothing was missing. It is currently using version 2.8.

I will be honest: I'm not sure what ADODB & DAO are exactly. Is there a way I can clean up the code relating to these?

 

[cheekybuddha]

You can try it like this and see if it makes any difference:

Private Sub UpdateFromP()
'pull data from linked P tables and update linked GIS geodatabase tables

Dim strSourceFields As String
Dim strDestFields As String
Dim rsPCIAGE As DAO.Recordset
Dim rs As DAO.Recordset

Set rsPCIAGE = CurrentDb.OpenRecordset("SELECT * FROM AAllAirportsAlbers ORDER BY ID;")

Set rs = CurrentDb.OpenRecordset("SELECT Sections.ID, BranchID, [Name], Use, InspPCI, PCISource, Area, ConstDate, InspDate, Round(DateDiff('m',[ConstDate],[InspDate])/12,0) AS InspAge " & _
"FROM (SELECT DISTINCT zUser_InspectionsAllYears.ID, Condition AS InspPCI, PCISource, Date AS InspDate, Area " & _
"FROM (SELECT ID, Max([Date]) AS LastInspDate FROM zUser_InspectionsAllYears GROUP BY ID) AS Query1 INNER JOIN zUser_InspectionsAllYears ON Query1.ID=zUser_InspectionsAllYears.ID " & _
"WHERE zUser_InspectionsAllYears.Date=[LastInspDate]) AS LastInsp RIGHT JOIN ((SELECT DISTINCT zUser_MajorMRAllYears.ID, zUser_MajorMRAllYears.Date AS ConstDate " & _
"FROM (SELECT ID, Max(Date) As LastInspDate FROM zUser_InspectionsAllYears GROUP BY ID) AS Q1 RIGHT JOIN ((SELECT ID, Max(Date) AS LastConstDate FROM zUser_MajorMRAllYears " & _
"GROUP BY zUser_MajorMRAllYears.ID) AS Q2 RIGHT JOIN zUser_MajorMRAllYears ON Q2.ID = zUser_MajorMRAllYears.ID) ON Q1.ID = zUser_MajorMRAllYears.ID " & _
"WHERE (((zUser_MajorMRAllYears.Date) = [LastConstDate] And (zUser_MajorMRAllYears.Date) <= [LastInspDate]))) AS LastConst RIGHT JOIN (SELECT [Uzukzxcttnsspyqmtdko] & [SectionID] AS ID, BranchID, Name, Use " & _
"FROM [Network-Extension] RIGHT JOIN (Branch RIGHT JOIN [Section] ON Branch.[_BUNIQUEID] = [Section].[_BUNIQUEID]) ON [Network-Extension].[_NUNIQUEID] = Branch.[_NUNIQUEID]) AS Sections ON LastConst.ID = Sections.ID) ON LastInsp.ID = Sections.ID " & _
"ORDER BY Sections.ID;")

While Not rsPCIAGE.EOF
    rs.Find "ID='" & rsPCIAGE!ID & "'"
    If Not rs.EOF Then
        rsPCIAGE!Branch = rs!BranchID
        rsPCIAGE!BranchName = rs![Name]
        rsPCIAGE!PCI = rs!InspPCI
        rsPCIAGE!Age = rs!InspAge
        rsPCIAGE!Area = rs!Area
        rsPCIAGE!Use = rs!Use
        rsPCIAGE!LastInspec = rs!InspDate
        rsPCIAGE!LastConst = rs!ConstDate
    End If
    rs.MoveFirst
    rsPCIAGE.MoveNext
Wend
rsPCIAGE.MoveFirst 'to commit final record edits
rsPCIAGE.Close
rs.Close

CurrentDb.Execute "UPDATE AAllAirportsAlbers Set AdjPCI = PCI - FormatNumber(DateDiff('m', nz(LastInspec,0), Date()) / 12, 0) * 3, AdjAge = Age + FormatNumber(DateDiff('m', nz(LastInspec,0), Date()) / 12, 0)"
CurrentDb.Execute "UPDATE AAllAirportsAlbers Set AdjPCI = IIf(AdjPCI < 0, 0, AdjPCI)"
CurrentDb.Execute "UPDATE AAllAirportsAlbers SET RepYear = RepairYear([PCI],IIf([LastInspec]>[LastConst],DatePart('yyyy',[LastInspec]),DatePart('yyyy',[LastConst])),IIf([Use]='Runway', 70, 60))"
CurrentDb.Execute "DELETE FROM ALLBranchPCIAge"
CurrentDb.Execute "INSERT INTO AllBranchPCIAge (FAAID, Branch, Use, SumArea, AveragePCI, AverageAge, WeightedPCI, WeightedAge )" & _
    "SELECT Totals.FAAID, Totals.Branch, Totals.Use, Sum(Totals.Area) AS BranchArea, Round(Avg([PCI]),0) AS BranchPCI, Round(Avg([Age]),0) AS BranchAge, " & _
    "Round(Sum([PCI]*[Area])/Sum([Area]),0) AS WPCI, Round(Sum([Age]*[Area])/Sum([Area]),0) AS WAge " & _
    "FROM (SELECT FAAID, Branch, Use, PCI, Age, Area FROM AAllAirportsAlbers GROUP BY FAAID, Branch, Use, PCI, Age, Area) AS Totals " & _
    "GROUP BY Totals.FAAID, Totals.Branch, Totals.Use;"

strSourceFields = "ID, [Name], Condition, [_Latest], PCISource, Use, [Date], Area, FAAID"
strDestFields = "SectionID, BranchName, Condition, Latest, Source, Use, InspectionDate, Area, FAAID"

CurrentDb.Execute "DELETE FROM PaverData_InspectionsAllYears"
CurrentDb.Execute "DELETE FROM PaverData_MajorMRAllYears"
CurrentDb.Execute "INSERT INTO PaverData_InspectionsAllYears(" & strDestFields & ") SELECT " & strSourceFields & " FROM zUser_InspectionsAllYears;"
CurrentDb.Execute "INSERT INTO PaverData_MajorMRAllYears(SectionID, BranchName, Use, ConstDate, FAAID) SELECT ID, [Name], Use, [Date], FAAID FROM zUser_MajorMRAllYears;"
CalcAges

End Sub

 

[ThatTransportationPerson]

I've attached the error that occured with this new code

 

[cheekybuddha]

Well I guess that's progress! You didn't crash Access!

It's late here. I'll have to get back to you tomorrow.

Others may well jump in in the meanwhile

 

[arnelgp]

try Re-linking first your tables to its proper BE if you can.

 

[cheekybuddha]

Hi, sorry, not had much time today.

There could be various reasons for your db not working from using different versions of Access to corruption of an old db ....

So, you will have to work through each potential problem , eliminating one at a time.

My first thought is that the query being opened is quite complex for Access - it usually baulks when there is more than a couple of outer joins. Was this mdb ever joined to and SQL server backend, or was it ever an .adp in a former life?

So that is the first thing to test:

Create a new query and switch to SQL view. Paste in the following SQL and see if you can run the query and get results:

SELECT
  Sections.ID,
  BranchID,
  [Name],
  Use,
  InspPCI,
  PCISource,
  Area,
  ConstDate,
  InspDate,
  Round(DateDiff('m',[ConstDate],[InspDate])/12,0) AS InspAge
FROM (
  SELECT DISTINCT
    zUser_InspectionsAllYears.ID,
    Condition AS InspPCI,
    PCISource,
    Date AS InspDate,
    Area
  FROM (
    SELECT
      ID,
      Max([Date]) AS LastInspDate
    FROM zUser_InspectionsAllYears
    GROUP BY
      ID
  ) AS Query1
  INNER JOIN zUser_InspectionsAllYears
          ON Query1.ID=zUser_InspectionsAllYears.ID
  WHERE zUser_InspectionsAllYears.Date=[LastInspDate]
) AS LastInsp
RIGHT JOIN (
  (
    SELECT DISTINCT
      zUser_MajorMRAllYears.ID,
      zUser_MajorMRAllYears.Date AS ConstDate
    FROM (
      SELECT
        ID,
        Max(Date) As LastInspDate
      FROM zUser_InspectionsAllYears
      GROUP BY
        ID
    ) AS Q1
    RIGHT JOIN (
      (
        SELECT
          ID,
          Max(Date) AS LastConstDate
        FROM zUser_MajorMRAllYears
        GROUP BY zUser_MajorMRAllYears.ID
      ) AS Q2
      RIGHT JOIN zUser_MajorMRAllYears
              ON Q2.ID = zUser_MajorMRAllYears.ID
    )
            ON Q1.ID = zUser_MajorMRAllYears.ID
    WHERE (
      (
        (zUser_MajorMRAllYears.Date) = [LastConstDate] And
        (zUser_MajorMRAllYears.Date) <= [LastInspDate]
      )
    )
  ) AS LastConst
  RIGHT JOIN (
    SELECT
      [Uzukzxcttnsspyqmtdko] & [SectionID] AS ID,
      BranchID,
      Name,
      Use
    FROM [Network-Extension]
    RIGHT JOIN (
      Branch
      RIGHT JOIN [Section]
              ON Branch.[_BUNIQUEID] = [Section].[_BUNIQUEID]
    )
            ON [Network-Extension].[_NUNIQUEID] = Branch.[_NUNIQUEID]
  ) AS Sections
          ON LastConst.ID = Sections.ID
)
        ON LastInsp.ID = Sections.ID
ORDER BY
  Sections.ID;

Report back with the results

 

[ThatTransportationPerson]

Hey Buddha,

Thank you so much for your help on this. I ran the query and Access crashed without displaying an error.

Was this mdb ever joined to and SQL server backend, or was it ever an .adp in a former life?

Not that I am aware of.

 

[cheekybuddha]

Hi, replies from me will be sporadic this week, as I am away and have limited (useful) internet access.

I ran the query and Access crashed without displaying an error.

This gives you a starting point.

Did you try re-linking the back end as per Arne's suggestion in post #16?

There are tables where the names are prefixed with a 'z' - these will not show up in the list of tables unless you change the option to show user and system tables.

If you have done that, then you need to break down the SQL statement in to smaller parts to see which bits work and which don't.

Try the following queries to see if you get results back:

    SELECT
      ID,
      Max([Date]) AS LastInspDate
    FROM zUser_InspectionsAllYears
    GROUP BY
      ID

    SELECT
      [Uzukzxcttnsspyqmtdko] & [SectionID] AS ID,
      BranchID,
      Name,
      Use
    FROM [Network-Extension]
    RIGHT JOIN (
      Branch
      RIGHT JOIN [Section]
              ON Branch.[_BUNIQUEID] = [Section].[_BUNIQUEID]
    )
            ON [Network-Extension].[_NUNIQUEID] = Branch.[_NUNIQUEID]

Let us know how you get on.

 

[ThatTransportationPerson]

Hello!

Sorry it has taken a month to get back to you. I got sidetracked with other projects that popped up, as well as a health emergency in the family.

I tried your queries, but as far as I remember, they did not work. The database kept crashing without any errors. I ended up completely scrapping the old database and building a newer, less complicated database that relies solely on a simple make-table query. This method is a bit more involved than the previous database was. I have to export the data from our "P" software, import it into this new database, run the query, and then join it to our polygon shapefile. But after 2 years of manually updating records, I think it's safe to say that this is an improvement!

Thank you again for your help! Maybe in the future I'll see if I can use this old database to update the polygon shapefile automatically.