Web & Report Q (v2000)

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.Value) 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!

Reply | Quote

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.

Reply | Quote

Bingo. That worked perfectly. Thanks a bunch!!

Reply | Quote

Bingo. That worked perfectly. Thanks a bunch!!

Reply | Quote

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.

Reply | Quote

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.Value) 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!

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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:

Re: Importing from Excel (Ex 2002)

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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:

Re: Importing from Excel (Ex 2002)

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

Reply | Quote