Access XP, Win2K Server, & IIS 5

[Chris]

Sorry for the cross post but hopefully this is the right place to find an
answer.

I am currently upgrading a Access 97 db to Access XP. No problems there.
I am also moving the db from a NT Server to a 2000 Server. Big Problems!!

It seems like half of my asp pages will not work for some reason. Let me
give you an example:

SELECT Reservations.*, Reservations.ID FROM [Reservations] WHERE
(((Reservations.ID)='JFWU17/17/20039:05:31 AM'))

results in the following error:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause

The following sql which is more complex results in no error:
SELECT [Ticket Sales Products].[Product ID], [Ticket Sales
Products].Description, [Group Rates].[Group Price], Format([Group
Rates].[Group Price],'currency') AS UP, [Ticket Sales Products].Class FROM
[Ticket Sales Products] INNER JOIN ([Day Price Codes] INNER JOIN [Group
Rates] ON [Day Price Codes].[Day Price Code] = [Group Rates].[Day Price
Code]) ON [Ticket Sales Products].[Product ID] = [Group Rates].[Product ID]
WHERE ((([Ticket Sales Products].Class)=1) AND (([Day Price
Codes].Date)=#7/17/2003#) AND (([Group Rates].[Price Schedule])='A')) ORDER
BY [Ticket Sales Products].Description

All of my problems seem to be random, although I know there is no such thing
as a random problem. Any sql that gives me an error, I have pasted into the
database and the query runs fine with no errors.

I have an INSERT statement that works great until you try to edit the record
a second time. Here is that statement:

UPDATE Reservations SET Reservations.ID='JFWU17/17/20039:05:31 AM',
Reservations.[Terminal ID]='JFWU', Reservations.User='Sharon',
Reservations.Shift='1', Reservations.Time='9:05:31 AM', Reservations.[Group
Code]='mA1002a', Reservations.[Reservation Date]=#7/17/2003#,
Reservations.Location='Jack Frost', Reservations.Salesperson='Barbara',
Reservations.Transportation=' ', Reservations.ArrivalTime=' ',
Reservations.Comments=' ' WHERE Reservations.ID='JFWU17/17/20039:05:31 AM'

The first time the record is edited it runs with no errors. If you go back
and change the record then the following error is produced:
Error Type:
Microsoft JET Database Engine (0x80004005)
The object specified cannot be replicated because it is missing a necessary
resource

I'm not trying to replicate, the table is a local table, all I want to do is
update the record using the same statement from above.

What could be causing all of these unusual errors? Right now the users are
running the same db with no problems. The only difference is that it is
Access 97 on an NT server. I am starting to lose my mind!!!!

Any and all help would be greatly appreciated!!!

[Bob Barrows]

Chris wrote:

> Sorry for the cross post but hopefully this is the right place to
> find an answer.

You must have multiposted. Please don't do that. A multipost is where you
post the same message several times to multiple groups. A crosspost is where
you put multiple newsgroup names in the To field and post it ONCE. This is
more acceptable. However, in this case, you have found THE perfect
newsgroup: your question concerns a database and asp code! This newsgroup is
asp.db: why post anywhere else?

>
> I am currently upgrading a Access 97 db to Access XP. No problems
> there.
> I am also moving the db from a NT Server to a 2000 Server. Big
> Problems!!
>
> It seems like half of my asp pages will not work for some reason.
> Let me give you an example:
>
> SELECT Reservations.*, Reservations.ID FROM [Reservations] WHERE
> (((Reservations.ID)='JFWU17/17/20039:05:31 AM'))

Why would you request the same column twice? What a waste of resources! You
shouldn't use SelStar (select *) in production code anyways. It impacts
performance, causes you to pull unnecessary data across the wire, and makes
debugging difficult.

>
> results in the following error:
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in FROM clause

Are you sure this is the statement causing the error? There does not seem to
be anything wrong with it ... oh wait! ID may be a reserved word - try
bracketing it the way you did the table name.

Are you running this in a saved query (recommended)? Or are you building the
SQL statement dynamically? If so, is the above statement the result of a
response.write of your sql string? If not, you should try doing that to make
sure the string contains the expected sql statement.

>
> The following sql which is more complex results in no error:

<snip>

> All of my problems seem to be random, although I know there is no
> such thing as a random problem. Any sql that gives me an error, I
> have pasted into the database and the query runs fine with no errors.
>

A good indication that you should be using saved queries.

> I have an INSERT statement that works great until you try to edit the
> record a second time. Here is that statement:
>
> UPDATE Reservations SET Reservations.ID='JFWU17/17/20039:05:31 AM',
> Reservations.[Terminal ID]='JFWU', Reservations.User='Sharon',
> Reservations.Shift='1', Reservations.Time='9:05:31 AM',
> Reservations.[Group Code]='mA1002a', Reservations.[Reservation
> Date]=#7/17/2003#, Reservations.Location='Jack Frost',
> Reservations.Salesperson='Barbara', Reservations.Transportation=' ',
> Reservations.ArrivalTime=' ', Reservations.Comments=' ' WHERE
> Reservations.ID='JFWU17/17/20039:05:31 AM'
>
> The first time the record is edited it runs with no errors. If you
> go back and change the record then the following error is produced:
> Error Type:
> Microsoft JET Database Engine (0x80004005)
> The object specified cannot be replicated because it is missing a
> necessary resource
>
> I'm not trying to replicate, the table is a local table, all I want
> to do is update the record using the same statement from above.
>
> What could be causing all of these unusual errors? Right now the
> users are running the same db with no problems. The only difference
> is that it is Access 97 on an NT server. I am starting to lose my
> mind!!!!
>
> Any and all help would be greatly appreciated!!!

We need to see the code being used to send these queries to Access. If
bracketing the ID name does not help, show us a snippet of code.

Bob Barrows

[Bob Barrows]

Chris wrote:

> I dynamically create the sql to use
> parameters easier. Should I not be doing that?
>

It depends.. It's much simpler and quicker to use saved parameter queries
where appropriate. For an action query, it takes a single line of code to
run it. For a read-only recordset, it takes two lines of code to return a
recordset. For example, if you save a query called aUpdTable with the
following sql:

Update table set colname = [p1] where col2=[p2]

You can call it from asp using (parmval1 and parmval2 are variables
containing the parameter values):

conn.qUpdTable parmval1,parmval2

Simple, huh? Notice you did not have to worry about delimiters, literal or
otherwise.

To open a readonly recordset:
set rs=createobject("adodb.recordset")
conn.qGetRecords parmval1,parmval2, rs

If you want an updatable cursor (you shouldn't be using a cursor to update
data - that's what UPDATE queries are for), you'll need to set the desired
cursortype and locktype properties before opening the recordset.

>
> That works but I really want to know why the following stopped
> working:
>
>

>>> Error Type: Microsoft JET Database Engine (0x80004005)

>

>>> The object specified cannot be replicated because it is missing a

>

>>> necessary resource

>

Sorry, but I can't help here - I've never seen this error.And my search of
the MS KB did not give me any results.

Bob Barrows

[Chris]

Thanks for the insight with the parameter queries. That error is really
haunting me though! I also cannot find any info about it. I even e-mailed
microsoft for an explanation of this error and they still haven't replied.
Would I see a performance increase by using saved queries?

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:e$obiMKTDHA.3636@tk2msftngp13.phx.gbl...

> Chris wrote:

> > I dynamically create the sql to use
> > parameters easier. Should I not be doing that?
> >

>
> It depends.. It's much simpler and quicker to use saved parameter queries
> where appropriate. For an action query, it takes a single line of code to
> run it. For a read-only recordset, it takes two lines of code to return a
> recordset. For example, if you save a query called aUpdTable with the
> following sql:
>
> Update table set colname = [p1] where col2=[p2]
>
> You can call it from asp using (parmval1 and parmval2 are variables
> containing the parameter values):
>
> conn.qUpdTable parmval1,parmval2
>
> Simple, huh? Notice you did not have to worry about delimiters, literal or
> otherwise.
>
> To open a readonly recordset:
> set rs=createobject("adodb.recordset")
> conn.qGetRecords parmval1,parmval2, rs
>
> If you want an updatable cursor (you shouldn't be using a cursor to update
> data - that's what UPDATE queries are for), you'll need to set the desired
> cursortype and locktype properties before opening the recordset.
>

> >
> > That works but I really want to know why the following stopped
> > working:
> >
> >

> >>> Error Type: Microsoft JET Database Engine (0x80004005)

> >

> >>> The object specified cannot be replicated because it is missing a

> >

> >>> necessary resource

> >

> Sorry, but I can't help here - I've never seen this error.And my search of
> the MS KB did not give me any results.
>
> Bob Barrows
>
>

[Chris]

Thanks for all your help. Just an FYI but I am working with MS Tech Support
now trying to find the cause and meaning of this error. Nothing like an
error that nobody has seen before to make life interesting! Thanks again.

"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:evukYjUTDHA.940@TK2MSFTNGP11.phx.gbl...

> Usually. YMMV.
>
> Bob
>
> Chris wrote:

> > Thanks for the insight with the parameter queries. That error is
> > really haunting me though! I also cannot find any info about it. I
> > even e-mailed microsoft for an explanation of this error and they
> > still haven't replied. Would I see a performance increase by using
> > saved queries?
> >
> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:e$obiMKTDHA.3636@tk2msftngp13.phx.gbl...

> >> Chris wrote:
> >>> I dynamically create the sql to use
> >>> parameters easier. Should I not be doing that?
> >>>
> >>
> >> It depends.. It's much simpler and quicker to use saved parameter
> >> queries where appropriate. For an action query, it takes a single
> >> line of code to run it. For a read-only recordset, it takes two
> >> lines of code to return a recordset. For example, if you save a
> >> query called aUpdTable with the following sql:
> >>
> >> Update table set colname = [p1] where col2=[p2]
> >>
> >> You can call it from asp using (parmval1 and parmval2 are variables
> >> containing the parameter values):
> >>
> >> conn.qUpdTable parmval1,parmval2
> >>
> >> Simple, huh? Notice you did not have to worry about delimiters,
> >> literal or otherwise.
> >>
> >> To open a readonly recordset:
> >> set rs=createobject("adodb.recordset")
> >> conn.qGetRecords parmval1,parmval2, rs
> >>
> >> If you want an updatable cursor (you shouldn't be using a cursor to
> >> update data - that's what UPDATE queries are for), you'll need to
> >> set the desired cursortype and locktype properties before opening
> >> the recordset.
> >>
> >>>
> >>> That works but I really want to know why the following stopped
> >>> working:
> >>>
> >>>
> >>>>> Error Type: Microsoft JET Database Engine (0x80004005)
> >>>
> >>>>> The object specified cannot be replicated because it is missing a
> >>>
> >>>>> necessary resource
> >>>
> >> Sorry, but I can't help here - I've never seen this error.And my
> >> search of the MS KB did not give me any results.
> >>
> >> Bob Barrows

>
>