Page 4 of 4 FirstFirst 1234
Results 46 to 51 of 51

Trying to automate access

  1. #46
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,454
    Hi John,

    First, design your tables. Then open the text file and start looping.

    Do you want to\he contour pairs to be stored in one field or split into two fields?



    For the tables, consider

    tblCallSigns
    -------------------
    CallSignID_PK - Autonumber
    CallSign - Text (KAXE, KCBS-FM, KISZ-FM, etc)
    Service - Text (FM, FX, FS, etc)


    tblContours
    ------------------
    ContourID_PK - Autonumber
    CallSignID_FK - Long (foreign key)
    Degree - Integer (1 - 360)

    LatLong - Text (47.17947 ,-92.67360)
    OR
    Lat - Double (47.17947)
    Long - Double (-92.67360)


    Tables linked on
    CallSignID_PK ------> CallSignID_FK


    Now, just a matter of writing the VBA code to parse each line in the text file and write the values to the tables.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  2. #47
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    25
    I managed to use Word to format a small part of the very large FCC text database of lat/long contours. Likely not the best approach and
    it took some time but got good results. The ^ up arrow symbol separates each stations data set. The table function in Word re-sized the data into the column format that I can directly use in the mapping program. Then I saved it in a text file and tried to import into an Access table without any real success. If I could retrieve each 360 row data set by callsign that would be great. The zipped text file of a small part of the main FCC file is attached. They update their main file of 60 dBu contours every day or two.
    So - Is this going off on a tangent or could this be a practical approach ? John
    Attached Files Attached Files

  3. #48
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,454
    Quote Originally Posted by TOWERSIGNALS View Post
    So - Is this going off on a tangent or could this be a practical approach ? John
    Speaking for myself, at this point I am totally lost as to what you want to do.

    You say:
    Quote Originally Posted by TOWERSIGNALS View Post
    Then I saved it in a text file and tried to import into an Access table without any real success.

    but you don't post your dB, any info about the table(s) design(s), nor what was wrong with the attempted import. Error messages??

    In the partial text file you posted, "PART 1-7.TXT", there are 222 stations. (I wrote a small sub to count the number of carats ("^") in the file. ).
    - Do you need/want the application ID number?
    - Do you need/want the Service? ("FM")
    - Do you want to save the File Number? ("BLED-1533")
    - Does the order of the Lat/Long pair matter? (there are no degrees for the pairs)



    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #49
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    25

    Unhappy

    Sorry Steve - it looks like that was out of left field, and I failed to explain things properly. Normally to get the end result of latitude/longitude pairs (example) 47.17947 , -92.67360 there are a number of steps needed, starting with a radius search, etc. etc. The FCC also publishes the 60 dBu contours for all the FM stations in the whole USA here :
    https://www.fcc.gov/media/radio/fm-s...ur-data-points
    They call it the FM_service_contour_current.zip and the data is already in the
    latitude/longitude pairs format and just separated by the "|" pipe symbol. It's all in one HUGE text file when you get done unzipping but it is difficult to work with because of its size. The other downside is that it is only the 60 dBu contours and often I need to generate 70, 54 or 40 dBu contours for a station. Still, I thought it would be handy to use it somehow and store away the results in an Access database for later retrieval.
    I tried to use a template offered in the Access 2010 toolbar but it seemed to strip some of the data (file number and service) and did not structure it into 360 rows. I really don't know what I'm doing with forms, tables or objects but have some tutorials I'm studying. My crude attempt is attached.
    Attached Files Attached Files

  5. #50
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,454
    John,
    I looked at your dB and was really confused as to why I couldn't open the tables in design view, until discovered it is a web dB design. I've never tried to use a web dB design.


    I found and downloaded "FM_service_contour_current.zip" - it is 199.5mb on my computer. Opening that text file using Notepad takes a long (long) time.
    I switched to NotePad++ -opens very fast and shows data line by line. There are 26,759 lines (stations) in the file.

    I wrote a sub to import (parse) the text file. It took 86 minutes.
    After the import, the dB file size was around 476mb.

    You said that you also might want/need 70, 54 or 40 dBu contours for a station. Can you generate a text file with those other dBu contours with all of the 26,759 stations?


    Here is the dB (no data) - it still needs a lot more development.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #51
    TOWERSIGNALS's Avatar
    TOWERSIGNALS is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2019
    Posts
    25
    Steve,
    Thanks for your time and the new ParseFccContourTextFile.zip Also thanks for telling me about Notepad++. Goodbye to old Notepad and hello to NotePad++. I still need to learn it but it appears to have Excel type abilities in structuring text files and YES - it is so much faster with the large FCC contour file. Just amazing !!
    I'm racing around today and tomorrow to finish a concrete footer before the cold rain arrives here Thursday night and Friday. More to communicate on Thursday.... John the Novice

Page 4 of 4 FirstFirst 1234
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