A2007 convert ADP forms to MDB forms?

[NKTower]

We found a number of serious performance bugs in Acess 2007 ADP that didn't
get fixed in Office 2007 SP1. Application runs fine in Access 2003. Most
serious problem is that if we so much as touch a record source or execute an
on-the-fly query Access 2007 seems to instruct SQL Server takes it upon
itself to completely reconstruct the extended attribute tables - 2,700+
queries with about a 15 second delay before client becomes responsive again.
We are now looking at going back to MDB/DAO/ODBC as test bed works fine in
Access 2007. We found a script that was supposed to turn off this process
but it doesn't seem to help.

For the real application we have so far built a new front end with links to
the 100+ tables (trivial.) and brought modules and class modules across
(trivial again.) But we don't see a way to bring forms across.

So far we have tried:
a) Get External Data - IMPORT - chugs along naming some forms as it goes,
then crashes, leaving invisible Access 2007 task, need to boot. Upon opening
the forms container is empty.
b) COPY / PASTE at form level between two instances of Access - no go.
c) COPY/PASTE at design level between two instances of Access - no go.
d) removed record source property and tried a,b,c above - no go
e) Tried steps a,b,c,d above an XP Pro SP2 machine with Access 2003 instead
of Access 2007 - still no way to migrate forms.

So - is there a way to copy the forms out of an ADP into an MDB short of
complete manual reconstruction? We have 180 forms or subforms in the forms
container.

 

[Sylvain Lafontaine]

No problem here copy/pasting or dragging/dropping at either the form or the
design level or importing from an ADP project toward a MDB file with Access
2003. I suspect that you might have some problem with an antivirus.
Another possible solution would be to first connect the ADP project to an
empty database and/or deleting some forms/rapports before doing the
importation. You could even try to first import your ADP forms (and
reports) into a blank ADP project (without any module/class module) to make
sure that you don't have some form of corruption.

 

[achang]

Microtools.us has a conversion tool, adptomdb that can copy adp objects and
SQL Sever table structures /views to an mdb file. The tool is not listed in
their product and order forms. You need to contact their support at
(e-mail address removed). Their website is http://www.microtools.us/.

 

[achang]

Microtools.us has a conversion tool, adptomdb that can copy adp objects and
SQL Sever table structures /views to an mdb file. The tool is not listed in
their product and order forms. You need to contact their support at
(e-mail address removed). Their website is http://www.microtools.us/.

 

[NKTower]

MicroTools's ADPtoMDB did it quite nicely. Took about 15 minutes to move 180
forms and 128 tables. Thanks for the reference.

 

[aaron.kempf]

have you considered VSS?

-Aaron

 

[aaron.kempf]

and honestly.. what type of performance problems are you talking
about?

what do you need some help building an index or something ROFL

-Aaron

 

[aaron.kempf]

have you tried these hotfixes?

http://support.microsoft.com/kb/936519/en-us

-Aaron

 

[NKTower]

It has nothing to do with indexes, thank you. The problem is that under
Access 2007, SQL Server is driven to executing 2,500+ queries which tweak
extended attributes on the server, where when running 2003 it doesn't. That
takes about 15 seconds wall-clock time. It is evident when running trace on
the server. I consider 2,500 unrequested queries to be a performance problem.

 

[aaron.kempf]

I don't.

I execute 2500 queries in a second _ALL_ the time.

Do you know how to properly use the SQL Profiler?

What information are you capturing?

Let me guess.. you're trying to capture _ALL_ the events and _ALL_ the
columns huh?

-Aaron

 

[aaron.kempf]

how about the HOTFIXES?

From Microsoft-- about Performance problems?

I just haven't had a problem with ADP so I'd love to find out more--
to make sure I don't hit the same problems that you're facing.

THanks

-Aaron
MCITP: Database Administrator

 

[aaron.kempf]

Are you writing custom extended properties?

I just wish I could help you.. I've never seen anything like this.

Do you use 'NOLOCK' in your queries?

Did you develop in SQL 2000 and then move to SQL 2005?
Have you used the 'copy database wizard' to copy all the tables into a
new database and see if you've got the same problem then?

I just really -- have been using ADP for a decade; and i've never seen
anything resembling 2,500 unrequested queries.

Are you sure that other people aren't using it?

If you had 20 people that were hitting a database server- and you ran
profiler-- it might show 2,500 calls for extended properties.

I just really haven't seen anything like this.. and I really honestly
would love to help.
Personally-- I'd rather chew on broken glass and then gargle with AIDS-
infested blood-- than go back to MDB.

-Aaron

 

[mirosoft news]

wrororire

 

[NKTower]

We had applied various hotfixes after the first tests, they didn't help. I
just returned to this post and see that it had some follow-up. For some
reason the "notify me" thing stopped. The link to hotfix published April 27
is clearly newer than our test, we'll have to try it.

The 2500 queries that are launched are after doing a single-column update
of a single row bound to a form - and a single record form at that. Can't
get simpler than that. SQL Profiler is referencing extended attributes and
it seems to launch queries touching most of our 130+ tables. Sincd same run
in Access 2003 didn't do any of that, we didn't dig much further. Gut feel
is that it touched attributes for every column in every table. (Most of
which are small lookup tables etc. perhaps a half dozen columns in 50 or so
rows.) I don't have the trace handy, I suppose we could regenerate it if we
really needed to so so. We have not explicitly touched or utilized any
extended attributes. Application started in Access 2003 with MSDE for
development, later SQL Server Express for development, SQL Server 2005 on
production servers. No problems there. Only when application was ported to
Access 2007 did it go bonkers.