Change refresh issue for ACCESS-based layers

[Anonymous]

I have a MGE 2008 layer which is based on an ACCESS table via the FDO
Provider for ODBC.

When I did some changes on the ACCESS table such as adding a text or
deleting a record, I didn't see the changes reflected in my AJAX map viewer
through zooming in or map refreshing, unless I resaved the feature source or
restarted the MGE server. Is it supposed to be like this? Why this is not
like a SDF3-based layer where the layer will instantly show the changes once
the updated SDF3 file is loaded into the unmanaged file folder? I
understand that the ACCESS-based feature source has the MDB data file as
well as a config file stored in the repository, and the data is cached on
the MGE server based on the timeout value (in my case, 24 hours, the
DataConnectionPool is enabled). I thought that the cache will only involve
the config/structure info of the connection, not the data content which
should be directly restrieved from the ACCESS MDB file. Am I right or
missing anything here?

If any record change made for an ACCESS-based layer has to go through a
feature source re-save or server restarting process, it's quite cumbersome.
I tried to disable the DataConnectionPool for ODBC(ACCESS), the AJAX viewer
response was very slow.

Of course I could consider to convert my ACCESS table into SDF3, but was
just wondering why this problem happened.

What about the SQL Server-based layer via ODBC? does it have the sampel
issue?

Thanks

[dswilson]

I just tried a test where the Access database sits on the server and is accessed via a DSN connection on the server. I previewed a layer with properties. I then opened the database, updated a record and saved the change to the table. I refreshed the layer and the change was there. I'm using the default settings for the provider. I also tried making changes and just panned and the values were shown. I used a label to see the changing values.

How are you updating the database? How are you commiting the change?

I wouldn't use SDF because you need an exclusive write lock to update it which means no one can query it at the same time.

Unfortunately I'm not seeing the problem.

Dave

[Anonymous]

Thanks Dave for the reply.

In my case, I updated the ACCESS MDB file on my XP, and then copied over and
overwritten the same file on the MGE server. Does that make a difference?

I could try to open and edit the ACCESS file on the MGE server directly, but
right now I don't have MS ACCESS installed on my MGE server. I was not
allowed to open and edit the ACCESS file (locked by MGE) across the intranet
via my network drive

Thanks

wrote in message news:5923726@discussion.autodesk.com...
I just tried a test where the Access database sits on the server and is
accessed via a DSN connection on the server. I previewed a layer with
properties. I then opened the database, updated a record and saved the
change to the table. I refreshed the layer and the change was there. I'm
using the default settings for the provider. I also tried making changes and
just panned and the values were shown. I used a label to see the changing
values.

How are you updating the database? How are you commiting the change?

I wouldn't use SDF because you need an exclusive write lock to update it
which means no one can query it at the same time.

Unfortunately I'm not seeing the problem.

Dave

[dswilson]

I think because the system has a read lock on the file it might be having issues with you trying to replace the file while it's open. Access may or may not support this.

In my case as you noted I'm editing the file live which Access supports. I used Access local on my machine as it's not common to have Access on the server. In my case my network permissions allowed this. Of course if you have rights to copy the file you arguably could create a DSN on your client machine and you should be able to work with it remotely.

Updating the physical file like you are doing isn't exactly how people envision using an Access database. That's not to say people don't require batch processes and such to update files on the server. I'm not sure how that is intended to work if you have a 24 hour timeout maintaining a lock on the file. I will have to research this some more.

Dave

[Anonymous]

Dave, i tested again with a direct access/edit on the ACCESS data file over
a network drive, and like you said, yes, my changes (added a test text for
maptips) were reflected in my map view instantly after each refresh (pan or
zoom-in).

So, my question is why the change in an overwritten ACCESS file is not
showed till a feature source resave or server restart is made? Direct
accessing and updating a production file of ACCESS db is not what we
normally do. What we normally do is first to update the file in dev, and
maybe test it with MGE in dev, and then deploy it in prod by overwriting the
ACCESS file.

Thanks

wrote in message news:5924110@discussion.autodesk.com...
I think because the system has a read lock on the file it might be having
issues with you trying to replace the file while it's open. Access may or
may not support this.

In my case as you noted I'm editing the file live which Access supports. I
used Access local on my machine as it's not common to have Access on the
server. In my case my network permissions allowed this. Of course if you
have rights to copy the file you arguably could create a DSN on your client
machine and you should be able to work with it remotely.

Updating the physical file like you are doing isn't exactly how people
envision using an Access database. That's not to say people don't require
batch processes and such to update files on the server. I'm not sure how
that is intended to work if you have a 24 hour timeout maintaining a lock on
the file. I will have to research this some more.

Dave

[dswilson]

I believe the issue is because the server has an open connection to the feature source in it's connection pool.

You can check this via the http://localhost/mapguide200x/mapagent/index.html pages and under Feature use GetFdoCacheInfo. If this lists a connection to your ODBC source for your database then you can't just overwrite the file.

I believe if you update the feature source by just opening it, entering the password and saving you will close and reopen the connection. You could try this after copying over your file.

Otherwise a server restart is required to close the connection or you would have to wait until the connection timeout has been reached.

Dave