Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing data from a .ASP file into a table (Access 2003)

    Another question that I have. I have a module wrote that will import data from a .ASP file into a table in my database, but I get an error 3027 about read only access. The data is comming out from a SAP database, and I would like to import it through excel. I can see the data no problem, along with the headings, but the import module keeps getting that error.

    Here's the code

    Sub GetDataFromExcel()

    Dim dbsTemp As Database
    Dim tdfLinked As TableDef

    Set dbsTemp = CurrentDb
    Set tdfLinked = dbsTemp.CreateTableDef("tblDowntime")

    tdfLinked.Connect = _
    "Excel 5.0;HDR=YES;IMEX=2;DATABASE=C:tempfrmDBReport.asp"

    tdfLinked.SourceTableName = "frmBDReport$"
    dbsTemp.TableDefs.Append tdfLinked
    DoCmd.RunSQL "Select * Into tbl1 From tblDownTime"

    End Sub


    Any thoughts?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    The extension .asp usually stands for "Active Server Page", i.e. a dynamically generated web page.

    Why are you using code to do this? The file name and table name appear to be fixed, so it would be easier to link to the file, if possible, and run an append query.

  3. #3
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    Hans,

    I am trying to import this information into my database. It is generated by a web server in our local intranet. The only problem that I have is that the file has to be generated in order to be read into excel, it is not automatically created. I thought I could use the code to import it. If you have a better idea, I am all ears.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    What exactly do you want to accomplish - do you want to import the file into Excel or into Access?

    Is there any chance you could attach a small sample file, zipped?

  5. #5
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    Hans,

    I would like to import this data into access, but when I export the data out of the SAP system, it only gives me options for a excel file. The zipped file would be hard to do since it is rather larger, over 300mb. I can set it up to export the data into a file, but the .asp file can be read by excel only. I would like to have a table set up in my database so that I can see what has been changed and by whom, so that we can track any changes made to the robot teach programs. Then we can better gauge the effectivness of the countermeasure that have been implemented.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    If you can open the file in Excel, try saving it from there as a workbook (.xls) or as a comma-separated values file (.csv). You should be able to import the resulting file into Access.

  7. #7
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    Is there a way to make it automatically import data into the existing table that is created when I import the data? I would like to have the file generated every couple weeks or so, and then have the database import it into the table. Is this possible?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    If you succeed in importing the .xls or .csv file created from the .asp file in Excel, it should be possible to automate it. Use DoCmd.TransferSpreadsheet for importing an .xls file, or DoCmd.TransferText for a .csv file.

    Oh wait, you could also try changing the extension of the .asp file to .txt or .csv, and see if you can import it directly into Access. If so, you can use DoCmd.TransferText to automate it.

  9. #9
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    Hans,

    Got it working great, but now I am told that there is no way to automate the SAP to report out the information into a file. I will have to manually generate the report, save it to my computer, and then import it. Since I am looking to do this at least once a month, I know i can run a append query to add the enteries, except I only want to add the new ones, and not have the ones that are already present be wrote twice. I wrote an append query to do this, but, it adds all the information, sometimes twice. You know a way to fix this?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    Is there a field or combination of fields that uniquely identifies a record?

  11. #11
    Lounger
    Join Date
    Sep 2007
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    Yes, there is a automatic number that is assigned to it. Usually, when I generate the report, the list starts at 1, and goes to whatever. But when I run the import spreadsheet module, it adds all the records, even if they already exist.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing data from a .ASP file into a table (Access 2003)

    Let's say this field is named ID. In the append query, add the following condition in the Criteria line for the ID column:

    Not In (SELECT [ID] FROM [NameOfTable])

    where NameOfTable is the name of the table you want to append records to. This condition excludes records with IDs that already exist in the table from being appended.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •