Page 3 of 3 FirstFirst 123
Results 31 to 43 of 43

Trying to automate access

  1. #31
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    20

    Gentlemen,
    The ParseFccDataFile4.mdb that Steve sent works very well - even though it's for one HAAT text file at a time. This alone is a big time saver for me.
    When I have Access open the .mdb file inside FCC_File_Parser_2000.zip folder, Access returns a message "Compile Error - Method or data member not found." I attached a Word copy of the error page I get.
    Is there a folder that I need to create first and then save somewhere to get this file to work ? I'm not sure where to put the Calculate HAAT text files that I would download. I realize it may be my Access 2000 that's the problem, so I'm hoping to acquire a newer laptop with A2010 or newer. Maybe the free runtime versions from Microsoft would work?
    Thanks, John
    Attached Files Attached Files

  2. #32
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    20
    I was able to download what seems to be Access Runtime 2010. I tried it on the .accdb file and it did open a nice looking form with four tables and station call signs. I tried to add another table but it returned a runtime error and shut down. Again possibel errors on my part.

  3. #33
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    Runtime will not allow you to do anything with the design of the database absent a full copy of MS access installed.

  4. #34
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    20
    You are correct about that. Runtime 2010 is not at all what I expected. While it does open the .aacdb files, it will only run the file and nothing more. Is there a way to save the Sognals.zip in the older .mdb format ? The form looks like it would be easy to use and load the uprocessed tables to a folder. Thanks,

  5. #35
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,124
    ?? In the zip from rpeare in post#30, the database is in mdb format.

    You mention --only 1 HAAT at a time??

    I'm not sure about the others, but I still must be missing something about your set up.

    If you can select the stations you want info for, then you could select and put them in some sort of list.
    Then use a loop construct to get the corresponding HAAT files for each of the stations in your list.

    Perhaps, Ssanfu or rpeare can add some details, but I think a looping structure would handle multiple stations to do whatever work is required per station.
    Code:
    Start:
    Select a list of station identifiers for processing
    For each station
      get the appropriate HAAT file
      do what  is required for this station
    Next station
    EndOfJob:
    Good luck.

  6. #36
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    20
    Orange,
    Yes - the multiple station list would be the most time efficient by far. In some cases I will have 15-20 stations using the Caculate HAAT tables that I download. Then, one by one, I convert them into the 2 column format that my FCC Curves program needs. That program gives me the 2 column results in bearing and distance data. Those results go into a client side HTML (javascript?) program that gives me 360 latitude/longitude pairs to plot the final contour on a map. I'll look again at post #30 and see if there was something I missed.
    Thanks

  7. #37
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,124
    It may not be something you missed in post#30. It's whether or not we're addressing the problem holistically. I still don't have the big picture clearly which is key to overall automation.

    What I see (quite cloudy I admit).

    Code:
    You have a list of Station call  letters.
    You have to go to FCC to get related info for each station and record/save it.
    For each of the sets of downloaded info
      do the calculation/manipulation to get 2 column data for each HAAT set and identify the new file.
    Next set
    'Not sure if this is where thee FCC Curves stuff fits
    'At this point all the data has been downloaded and stored in its own 2 column format
    For each set of 2 column data
     Process that set with your javascript program and store the result in a named file
    Next set
    Then:
    For each named file
      Plot the contour map
    Next named file.
    You can edit, revise, delete the steps as necessary. The overall process and the intermediate steps are key to automation of your set up.

  8. #38
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,355
    I'm still confused on the order of events....

    It appears that you have to go to a FCC web site, enter in some station data, calculate(?) the HAAT and somehow save the data to a text file?
    You do this for many station call signs, then want to parse all/each of the data files into two columns? Or you want to parse the text file and save the HAAT data directly into a table (instead of writing to a text file?)
    I could add the looping to parse many files and add code to save the parsed data into a table, but rpeare's solution is doing that now.

    Also not sure what all of the code in the original "Fcc Curves.mdb" is for or what it does......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #39
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    20
    Gentlemen,
    Earlier this year I had to prepare an Exhibit (a map) demonstrating that at least 15 competing station contours overlapped one or more of 5 station contours owned by a client of mine. He owned 4 and wanted to buy a 5th station. Generally, the FCC prohibits a company from owning a large percentage of the stations in a given area. If all 15 contours showed an overlap with one or more of his 5 station contours, they usually approve the deal. So the process of creating the attached map was as follows :

    1. I picked a point on the map (more or less centered) in the middle of the 5 client owned stations. That latitude/longitude was entered in the FCC's FM Query as a radius search.

    2. The search returned a rather lengthy list of stations (including my client's 5 stations) that I saved as an "Alldata".html file with all the links. Using Excel I sorted this list by distance, callsign etc. and deleted some rows that would not apply in this specific case.

    3. Going online with my "Alldata".html file & via the Calculate HAAT links, I downloaded 20 tables from my list and saved each as "CallsignHAAT".txt files (5 client +15 competitors)

    4. One by one, the "CallsignHAAT".txt tables were converted into the 2 column .txt format that FccCurves.mdb needs and named "CallsignHAATReady".txt This has been one of the more tedious steps.

    5. Using Access, the FccCurves.mdb returned each of the CallsignHAATReady files as a 360 row, 2 column data set it names Target.txt. I renamed each Target.txt file as a "CallsignDistance".txt file. Then I repeated this step for all 20 HAATReady files.

    6. One by one the "CallsignDistance".txt files were opened, data copied and then pasted into a Javascript program that produces 360 rows of latitude/longitude pairs in comma delimited format. Those were saved as a "Callsign Plot".txt files.

    7. Finally each "Callsign Plot".txt file was imported into my Topo program to create the desired contour of each station.

    The modified FCCCurves.mdb has several options but I normally use the F(50,50) service contour option, followed by Select Channel Range: FM radio and TV transmit Channels 2-6, enter the stations ERP in kilowatts, then the desired field strength in dBu (70 in the example above). I leave the HAAT field empty and it calculates each row and returns the list as target.txt. It's actually each compass bearing vs. distance in kilometers. This was a process that used to take hours but now gives me the "Target" file in just seconds.

    Thanks once again - I'm open to any and all suggestions.
    Attached Thumbnails Attached Thumbnails COMBINED MARKET.jpg  

  10. #40
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,355
    OK, John, you were not crazy. I dusted off one of my old confusers (XP and Office 2000) tried rpeare's dB. It would not run. I traced it down to command for the list box -> .AddItems. I researched for about 30 min - couldn't find any reason for not being able to get the .ADDITEM method to work. I finally modified the code to get the list box filled.

    And I copied the wrong file!!
    I will have it tomorrow.


    In the mean time, in your example text file "KPRT-FM Calculate HAAT DATA.txt", when converting it to 2 column format, do you want the data in a table or in a text file?
    (is the "CallsignDistance.txt file name like "KPRT88.txt" ??)



    Step 3 above - you download text files with the file name being in the format of "CallsignHAAT".txt".
    Examples:
    KTJSHAAT.txt
    KJCMHAAT.txt
    KKVO-C3HAAT.txt
    KQTZ-CHAAT.txt


    Step 4 - the "CallsignHAAT".txt" file is parsed to 2 columns and renamed "CallsignHAATReady".txt"??
    Example:
    KTJSHAATReady.txt
    KJCMHAATReady.txt
    KKVO-C3HAATReady.txt
    KQTZ-CHAATReady.txt


    Step 5 - You say "the FccCurves.mdb returned each of the CallsignHAATReady files as a 360 row, 2 column data set ". But didn't Step 4 parsed the txt file into a 2 column, 360 line text file??
    What sub/function in the FccCurves.mdb does this?

    Re-naming the text file to "CallsignDistance.txt" is easy while parsing.


    As you can tell, I'm still trying to understand the steps...
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #41
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    20
    Greetings,
    The output of FccCurves.mdb (Target.txt) is in a 360 row / 2 column format required by the client-side Javascript program that generates the 360 latitude/longitude pairs. I'll send a copy of the Javascript for all to review but for now I did not want to "muddy the waters".
    The Javascript 2 column output is also in the format that my Topo mapping program needs to plot these points on a map as in the 20 station exhibit above. So converting the FCC's Table HAAT data into this format seems to be the best move.
    Because the FccCurves.mdb needs a file path for the source, it seems that separate station files will be required. However, it will process 2 or more files end to end, but it keeps counting up from 360 instead of starting over at 1,2,3 etc. for the second file. I've attached a (Source) Testfile and the Target file results for your review.
    If the FccCurves.mdb could be told to start over at "1" then a single text file could be used for a source and a single target file would contain the results. I think rpeare had suggested something like this. Maybe the results could even be sent to an Access database for later retrieval...
    Finally, I'm still working to get a newer full version of Access 2003 0r 2010 - the Runtime version seems all but useless.
    Attached Files Attached Files

  12. #42
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,355
    Ok, I've been reading your posts again and looking at the web sites. See if this is close:

    Step 1 : you go to the FCC FM-Query Search web site and enter the a radius and the Center Coordinates (NAD 27), then click a button.
    Step 2 : you get a page of FM Query Results, do some filtering on the results and end up with a list of Stations.
    Step 3 : you click on a call sign and another FM Query Results page appears.
    Step 4 : in the FM Query Results from Step 3, clicking on "Calculate HAAT" results in this page. I'm not sure how you get the data saved to a text file. You rename each file to "CallSignHAATData.txt".


    So now you have X number of files (20 in your example in Post #39) that look like
    Code:
    Antenna Height Above Average Terrain Calculations -- Results
    Input Data
        Latitude      36 41' 45" North
        Longitude   108 13' 23" West    (NAD 27)
    
        These coordinates convert to NAD 83 coordinates of
          36 41' 45.01", North, 108 13' 25.27" West (NAD 83).
    
        Height of antenna radiation center above mean sea level: 1802 meters AMSL
    
        Number of Evenly Spaced Radials = 360       0 is referenced to True North
    
    Results
    
        Calculated HAAT = 88 meters
    
    Antenna Height Above Average Terrain calculated
    using 1 km GLOBE terrain data
    
    Individual "Radial HAAT" Values, in meters
    
      0   145.2 m    120    72.0 m    240    60.6 m    
      1   148.0 m    121    70.2 m    241    61.5 m    
      2   147.5 m    122    67.8 m    242    62.4 m    
      3   146.2 m    123    65.1 m    243    63.5 m    
      4   145.3 m    124    62.7 m    244    64.9 m

    Step 5 : Next convert the 6 column text files to 2 column text files renamed to something like "CallSignHAATDataReady.txt".
    The data in the text file looks like
    Code:
    1   148.0 m
    2   147.5 m
    3   146.2 m
    4   145.3 m
    5   143.2 m
    6   138.9 m
    7   132.5 m
    8   127.4 m
    9   122.1 m
    10   115.9 m
    11   111.1 m
    12   107.1 m
    13   103.4 m
    14    99.8 m
    15    96.2 m
    etc.

    Step 6 : Using "FccCurves.mdb", the "CallSignHAATDataReady.txt" files are processed and renamed "CallSignDistance.txt"
    The text file data now is
    Code:
     1    139449
     2    139212
     3    139081
     4    139010
     5    138963
     6    138844
     7    138713
     8    138558
     9    138391
     10    138224
     11    138033
    In "FccCurves.mdb", you set:
    Select Contour Type = F(50,50) service contour option
    Select Channel Range = FM radio and TV transmit Channels 2-6
    ERP (in kilowatts) = 6
    Field strength (in dBu) = 70
    HAAT = empty

    Q1) Distance is left empty?
    Q2) If you had 20 text files, would all 20 files use the same parameters?
    Q3) What is the RESULTS control for?
    Q4) What does the FIND button do?


    Finally, the "CallSignDistance.txt" files are copied into the JaveScript program to create latitude/longitude pairs in a CSV file for plotting.


    Am I close??


    -------------------------------------------------------------------------------------------------------------
    -------------------------------------------------------------------------------------------------------------

    I didn't forget. Here is rpeare's dB that should run in your computer.
    I used Win XT with Access 2000. It runs in my computer, so it should run on your computer.

    The function "PopulateList" is what I modified.
    Code:
    <snip>
      With lstFiles
        For Each fsFile In fsFolder.files
          sFile = fsFile.Name
          sCall = Left(sFile, InStr(sFile, " ") - 1)
          sBand = Right(sCall, Len(sCall) - InStr(sCall, "-"))
          sCall = Left(sCall, InStr(sCall, "-") - 1)
          '          .AddItem sCall & ";" & sBand & ";" & sFile
          tmp = tmp & sCall & ";" & sBand & ";" & sFile & ";"
    '      Debug.Print tmp
        Next fsFile
        .RowSource = tmp
      End With
    <snip>
    Attached Files Attached Files
    Last edited by ssanfu; 09-20-2019 at 05:17 PM.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #43
    TOWERSIGNALS is offline Novice
    Windows 7 64bit Access 2002
    Join Date
    Sep 2019
    Posts
    20
    Steve,
    The process you have outlined is correct. I just highlight the data (in step 4) down to the last row in the table, click Copy then Paste it into a text file with the name of the station - "CallsignHAAT".txt file.
    Normally I use the "FCC 30 second terrain database (continental USA only)" button choice rather than 1 km GLOBE terrain data option. Not sure if that matters to the code ?
    In Step 6 all the parameters are the same with the exception of "ERP (in kilowatts) = " This input is different for most of the stations on the list. Some broadcast at 100,000 watts, some at 25,000 watts and some even less than 6,000 etc. For the results to be accurate the individual ERPs must be entered.
    I overlooked this - so in theory then, only stations with identical ERPs could be run together on a list and there would still be the problem of the output file count starting at 361 for the second station, 721 for the third set of results etc.
    When I open FCCCurves.mdb form on the right side there are 3 sets of buttons. Under "Find This:" I select the button that reads "Distance, given a Field Strength (in dBu)" This automatically blanks out the box Distance (km) on the left side of the form.
    The HAAT box is left blank but the desired "Field Strength (dBu)" box must have a numerical value - typically 60 or 70.
    The "Find Result" button was used only on the original FCC Curves program found here: https://www.fcc.gov/media/radio/fm-a...agation-curves
    It does everything this modified FCCCurves.mdb does but only one calculation at a time. That used to take me a couple of days to process a list of 30 or 40 stations.
    I'll try the FCC File ParserMod.zip later today and let you know how it works. Thanks once again Steve (and to all) for your time spent on this.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-20-2019, 10:42 AM
  2. automate importing access
    By mcchung52 in forum Access
    Replies: 1
    Last Post: 01-21-2012, 02:20 AM
  3. Automate report on ACCESS
    By Schoolo in forum Database Design
    Replies: 1
    Last Post: 12-18-2011, 02:11 AM
  4. Automate Access Queries
    By andreala in forum Queries
    Replies: 5
    Last Post: 08-23-2011, 06:01 PM
  5. How to automate a access query ... Help me
    By kusamharsha in forum Programming
    Replies: 0
    Last Post: 02-25-2009, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums