Replies

You can currently host an Access Web application along with the database (it’s converted to a sharepoint list when you publish it) at http://accesshosting.com. This is available free for developers to test out 2010 apps.

The link for accesshosting needs an edit. The period after com gets picked up and throws an error. I toyed with Sharepoint and 2007 last year very briefly. I found the conversion to Sharepoint lists not too difficult, but the performance hit to the rather complex database was huge compared to network-based logging in to the backend and terminal services. Changing a record was a 10-15 second wait over the web versus virtually instantaneous for the network based version (backend frontend).

Another web-based possibility, particularly if the application is straightforward is to look at ASP pages accessing the Access database which is stored on a web server. I played around with an application a few years ago and used ASPMaker. It was a volunteer effort and I ran out of time as the database was a little complicated. Again, if info isn’t too complex then this might be a possiblity.

Peter N

I thought about that shortly after I posted. I always work with DAO and have never been comfortable with ADO. My guess is that it is something to do with establishing the remote connection but I’m out of my depth at that point. In your original code, it just seemed to me a little inefficient to loop through the recordset rather than just run a query. Not sure how running SQL fits into the picture. As Paul suggests, maybe this is better run on the SQL Server end. One final thought and again, this may not be possible given the web-based setup: Can you link your Access database to the table in question in SQL Server? Then I think Access would see it as local. Just a thought. Over to people who know better

After you connect, what happens if you change your sql string to a simple append query and use docmd.runsql rather than looping through the recordset? Since it is an append query, if you don’t want the warnings that go with append queries you can add docmd.SetWarnings False before and True after to kill the warnings.

SQL1 = “INSERT INTO Local_Table ” & _
“SELECT Remote_Table.* ” & _
“FROM Remote_Table;”

docmd.SetWarnings False
docmd.runsql
docmd.SetWarnings True

Peter

Did you specify your parameter field as a parameter in the query properties? [forms]![frmReports]![txtyear] in the parameter side and the datatype (either date or integer depending on what is in that field). Crosstabs will fail without these specified.

Peter

Thanks, John. That is what I was hoping to find out. I’ll post back if I have any questions.

Peter

For those interested. Here is the database I talked about previously.

Hi Tom: I revised my thinking. I have a stripped down database that does what you need unfortunately I can’t get it to upload. I’ll try to do it later today.

Retirement Homes is nothing more than a lookup table with a list of all the homes. For printing, you have a query that filters out the homes with no residents. The form for printing the report has two options, all or any single home. (It is possible to do a multi select so you can choose more than one home, but that is a lot more complicated).

Let me know if you have any questions.

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Assuming you have a join table for MembersinRetirement homes i.e. three tables, Members, Retirement Homes, and MembersinRH.
MembersinRH has two fields MemberName(or better yet ID), HomeName and the two fields together are the primary key (which means that you can’t accidentally put one person in two homes)
Base your lookup on a query of retirement homes for the report on MembersInRH and set the HomeName to unique values and then the list your secretary sees for this report will only be the homes that have people in them. She chooses one or all. As long as this query is the basis for filtering your records, you won’t get any homes without members resident in them.

mmmm will give you the full name. mmm gives the three letter short form.

mmmm will give you the full name. mmm gives the three letter short form.

mmmm will give you the full name. mmm gives the three letter short form.

mmmm will give you the full name. mmm gives the three letter short form.

mmmm will give you the full name. mmm gives the three letter short form.

mmmm will give you the full name. mmm gives the three letter short form.

mmmm will give you the full name. mmm gives the three letter short form.