Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Web & Report Q (v2000)

    I'm not sure of the normal protocol; whether to ask two questions in one posting but here goes:

    - Is it possible to link an access database to a table on the web? I thought it was impossible but I recently got a taste of an XML link via Excel so I thought it was worth asking again.

    - I have an SQL statement that builds as a user makes selections on a form. The SQL statement builds in VBA and updates my subform quite nicely. How do I get a report to run based on the newly created SQL statement? Should I have the code create a query or is there a way to do it via the record source of the report?

    Thanks!

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

    Re: Web & Report Q (v2000)

    About your second question: you can set the record source of a report to an SQL string in the On Open event of the report. So if you store the SQL in a global variable, you can do it like this:

    Private Sub Report_Open(Cancel As Integer)
    If Not strSQL = "" Then
    Me.RecordSource = strSQL
    End If
    End Sub

    If strSQL has been set, the report uses it as record source, otherwise it opens with the record source specified in design view.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web & Report Q (v2000)

    Hans,
    Thanks! I tried a few unsuccessful attempts using the OnFormat property instead of using the OnOpen property. How do I store the SQL in a global variable?

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

    Re: Web & Report Q (v2000)

    Declare the variable in a standard module (the kind you create by clicking New in the Modules section of the database window, or by selecting Insert | Module in the Visual Basic Editor:

    Public strSQL As String

    You wrote that you already build the SQL statement in VBA, so you only have to assign the end result to this variable:

    strSQL = whatever you built

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web & Report Q (v2000)

    Hans,
    Sorry about my struggle to understand. I haven't worked much with modules...only embedded VBA. I receive the following error: Invalid Outside Procedure.
    Here's a bit more info: The user selects criteria in a form then clicks the cmd button "Begin Search" that returns only records meeting the criteria. The code I use is:
    >>>>>>>>>>>>>>>>
    Private Sub cmdBeginSearch_Click()

    Dim MyDB As Database

    strWorkSQL = "SELECT * FROM tblWorks WHERE WorkID IN (SELECT tblWorks.WorkID FROM tblWorks "
    If Not IsNull(Forms!frmComposerSearch!cbxTitleOfWork.Valu e) Then
    strWorkWhere = strWorkWhere & " WHERE ([WORK] LIKE " & Chr(34) & Forms!frmComposerSearch!cbxTitleOfWork.Value & Chr(34) & ")"
    Else
    strWorkWhere = strWorkWhere & " WHERE (([WORK] LIKE '*') OR ([WORK] Is Null))"
    End If

    strWorkSQL = strWorkSQL & strWorkWhere & ") ORDER BY WORK;"
    Set MyDB = CurrentDb
    Forms![frmComposerSearch]![Works Form].Form.RecordSource = strWorkSQL
    Forms![frmComposerSearch]![Works Form].Form.Requery

    End Sub
    >>>>>>>>>>>
    This code is triggered when the user clicks 'Begin Search' and this criteria is what I want to use for the report.
    Thanks again!

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

    Re: Web & Report Q (v2000)

    The declaration (Public strSQL As String) goes near the top of a module, before any procedures or functions in that module. But you can only assign strSQL a value within a procedure or function. In your example, you would insert a line

    strSQL = strWorkSQL

    above the End Sub of your cmdBeginSearch_Click event procedure.

    By the way, is frmComposerSearch the form that contains cmdBeginSearch? If so, you can replace Forms!frmComposerSearch by Me. Me refers to the object (form or report) running the code.

    By the way 2: you can omit the Else part of the If ... Then ... Else ... End If.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web & Report Q (v2000)

    Bingo. That worked perfectly. Thanks a bunch!!

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Web - Link HTML Table (v2000)

    In ref to Q #1: Is it possible to link an access database to a table on the web? I thought it was impossible but I recently got a taste of an XML link via Excel so I thought it was worth asking again.

    The answer, in A2K anyway, is probably NO. According to MSKB 180105:

    Description of supported data sources for importing, exporting, and linking in an Access 2000 database

    "Microsoft Access 2000 can import or link data from other Microsoft Access databases (version 2.0, 7.0/95, 8.0/97 and 9.0/2000), as well as data from other programs and file formats, such as Microsoft Excel, dBASE, or Paradox. You can also import or link (read-only) HTML tables and lists that reside on your local computer, network server, or Internet server."

    (Emphasis added.) Read that last clause carefully - what this means is, you can link an HTML table, on a local PC, network, Intranet, or Internet server, but NOT from some web site out there on the Internet. This explained in MSKB 237590:

    ACC2000: "Import/Link Data on an FTP or HTTP Server" Help Topic Incorrect

    "The Microsoft Access 2000 Help topic "Import or link (read-only) data on an FTP or HTTP Internet Server" incorrectly states that you can import or link data from a remote HTTP server. ... Microsoft Access 2000 does not support importing or linking data from a remote FTP or HTTP server."

    Recommended "Resolution": "Save the file or HTML page to your hard disk or Intranet site, and then import or link the file from there." See this previous post:

    <!post=Re: Importing from Excel (Ex 2002),362428>Re: Importing from Excel (Ex 2002)<!/post>

    This post has some sample code that can be used to download HTML table from a web site, save locally, and import into Access. Depending on format of the web page, and the table, this may or may not be a simple procedure (or possible at all). I don't know if there has been any change to the limitation described in MSKB 237590 in the more recent versions of Access (2002/2003), or if an XML link would work - according to MSKB 826507 (Supported data sources for importing, for exporting, and for linking in a Microsoft Office Access 2003 database), in A2003 you can import/export to XML files, but not link.

    HTH

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Web - Link HTML Table (v2000)

    Mark,
    Fantastic post! Thanks a bunch. I've never been able to find solid answers to that question so I really appreciate you sharing that information.

Posting Permissions

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