Access 2007 + SQL Server

[nutraf]

Hi,
i'm planning the migration of my Access97 DAO 3.5 project with mdb
backend onto Access 2007 + SQL Server. (Express Edition)

Some Questions:


1) In my actual A97 project i'm using different menubar for each form.
Is there a way to use it again in Access2007 or i have to mess with
new ribbon system?


2) Which is the best way to link my Access2007 project to SQL? DAO
+ODBC, ADO+ADP (if already exists)


3) If i deploy my Access2007 project to my customers how can i deploy
even SQL Server DB in the same setup?


4) How can I manage my customers different SQL DB on my development
PC? Backend is almost the same for all the customers but may be
different in certain case. How can I attach and deattach the same file
(i.e. books.mdf) in order to edit feature for customers request?


Thank You in advance
Rob

 

[Danny J. Lesandrini]

Rob:

I'm not an expert on Access 2007 but here's what I think.

1. Your custom toolbars will automatically show up in the ribbon but will
require a click for the users to see, making them a little useless. At least
that was my experience. Maybe someone knows better on that and/or
Microsoft supplied a fix since I last upsized such an app.

2. I've built 2 ADPs in the last year and I think that's a good way to go,
but you said you're upsizing, so the *best* way to go is the way that
requires the least re-work, and if you have a lot of DAO code to convert,
then going ADP will require rework. Personally, I would probably try to
avoid that if I could.

How many users and how big is the app? That might make a difference.

3. I suspect that if you are responsible for deployment, you would be
installing SQL Express. Years ago I built a setup routine to deploy the
MSDE engine. I'm sure things have changed, but basically, the article
below describes what I did. It may be useful on some level.

http://www.databasejournal.com/feat...271/Perform-An-Unattended-Install-of-MSDE.htm

I'll be curious to see how others respond. Great questions!

 

[nutraf]

Danny thank you for your reply.

Concerning to ADP, I've a large project with 100 customers
installation, so I know the hard question is to rewrite or not with
ADO code. I also use unbound mode so I have a lot of code in my
application.

I'm very suprised that Microsoft said to prefer oldstyle ODBC access
to SQL with Access2007, could it be a preview of ADP's dead?

In order to deploy SQL db i need this feature to be automatic (if
possible) because i have many customers far away from my company.

Rob

 

[Danny J. Lesandrini]

When I asked about how many users, it was with a view to decide if
the time necessary to rewrite to ADO was worth it. There are folks
who believe DAO *is* the way to go, and I can't argue against it, but
if it's pure Access to SQL Server, I prefer using ADO.

In the ADP apps I recently built, I attach the ADP to a dummy-hub db.
All actual data connections are done via ADODB Recordsets which are
built on the fly and slammed into forms, reports and controls.

If a user opens the database window, they see NO tables. The only
way to get to data is through my forms, where I can control things.

So what I'm saying is that if it were me, and if I had the time, I'd
rebuild it with ADO recordsets. I don't believe ADPs are near death,
but I'm not exactly connected with the Microsoft Product Team.

As for the deployment of SQL Express, I'm sure there is a way to do
it, but for the life of me, I couldn't find another newsgroup post. My
article about deploying MSDE is out of date, but roughly what you
need. I built a VB App that managed the installation, but there's got
to be a cleaner way to do that. I just don't know what it is.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Danny thank you for your reply.

Concerning to ADP, I've a large project with 100 customers
installation, so I know the hard question is to rewrite or not with
ADO code. I also use unbound mode so I have a lot of code in my
application.

I'm very suprised that Microsoft said to prefer oldstyle ODBC access
to SQL with Access2007, could it be a preview of ADP's dead?

In order to deploy SQL db i need this feature to be automatic (if
possible) because i have many customers far away from my company.

Rob

 

[nutraf]

When I asked about how many users, it was with a view to decide if
the time necessary to rewrite to ADO was worth it.  There are folks
who believe DAO *is* the way to go, and I can't argue against it, but
if it's pureAccesstoSQLServer, I prefer using ADO.

In the ADP apps I recently built, I attach the ADP to a dummy-hub db.
All actual data connections are done via ADODB Recordsets which are
built on the fly and slammed into forms, reports and controls.

If a user opens the database window, they see NO tables.  The only
way to get to data is through my forms, where I can control things.

So what I'm saying is that if it were me, and if I had the time, I'd
rebuild it with ADO recordsets.  I don't believe ADPs are near death,
but I'm not exactly connected with the Microsoft Product Team.

As for the deployment ofSQLExpress, I'm sure there is a way to do
it, but for the life of me, I couldn't find another newsgroup post.  My
article about deploying MSDE is out of date, but roughly what you
need.  I built a VB App that managed the installation, but there's got
to be a cleaner way to do that.  I just don't know what it is.
--
Danny J. Lesandrini
(e-mail address removed)

Danny thank you for your reply.

Concerning to ADP, I've a large project with 100 customers
installation, so I know the hard question is to rewrite or not with
ADO code. I also use unbound mode so I have a lot of code in my
application.

I'm very suprised that Microsoft said to prefer oldstyle ODBCaccess
toSQLwith Access2007, could it be a preview of ADP's dead?

In order to deploySQLdb i need this feature to be automatic (if
possible) because i have many customers far away from my company.

Rob









- Mostra testo citato -

Hi Danny,
just another question.
If i decide to reduce code rewriting in ADO syntax and so prefer DAO
+ODBC strategy, Can I realize a client/server configuration?
If I execute a query from my access 2007 app Can sql server give me
back only the result records? Or it give me back the entire cursor and
my DAO engine have to filter client-side? as u can notice i'm
considering even performance in my choice

Thank you in advance
Rob

 

[Danny Lesandrini]

Rob:

This is, as one says in Russian, a "spornie vapros" ... a controversial question.

It's been said that DAO requests to SQL Server return entire recordsets and
do slicing/dicing in the client, but I have never been able to confirm that, and
my experience suggests that's not happening.

Regardless, you can, and should, circumvent the issue altogether with the use
of Pass Through queries. (from the Queries menu, SQL Specific | Pass Through)

I often put the hard crunching logic in a SQL Server view or stored proc and
access it directly through a Pass Through query. I recently applied this process
to a report whose recordset was bogging down. It went from over a minute to
open to just a few seconds.

If you choose to use DAO + ODBC, performance needn't suffer, IMO.
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi Danny,
just another question.
If i decide to reduce code rewriting in ADO syntax and so prefer DAO
+ODBC strategy, Can I realize a client/server configuration?
If I execute a query from my access 2007 app Can sql server give me
back only the result records? Or it give me back the entire cursor and
my DAO engine have to filter client-side? as u can notice i'm
considering even performance in my choice

Thank you in advance
Rob

 

[AG]

Rob,

'it depends'.
I am in the process of converting a large Access 2000 app from using a mdb
back end toAccess 2007 & SQL Server 2005 back end, and am using DAO, ODBC
and passthrough queries.
Access 2007 doe seem to play much nicer with SQL Server 2005 than Access
2000 did with SQL Server 2000.

You need to use SQL Server Profiler to monitor what Access is passing to SQL
Server and how many rows are being returned.

Simplified, if your 'request' to SQL Server includes joins, where clauses,
etc. that can be easily applied on the server, Access does an excellent job
of that and only the necessary rows and columns are returned. This includes
group by queries and 'Select Distinct'.
Don't use Jet's 'Select Distinctrow', as that does not exist in SQL Server.

However, (in many cases) if you try to join an Access query with a SQL
table/view, the SQL must return the entire table/view and Acess (Jet) does
all the work. For cases like that, I either create a SQL view, use a
passthrough query or a stored procedure (called via a passthrough query).
--

AG
Email: npATadhdataDOTcom

When I asked about how many users, it was with a view to decide if
the time necessary to rewrite to ADO was worth it. There are folks
who believe DAO *is* the way to go, and I can't argue against it, but
if it's pureAccesstoSQLServer, I prefer using ADO.

In the ADP apps I recently built, I attach the ADP to a dummy-hub db.
All actual data connections are done via ADODB Recordsets which are
built on the fly and slammed into forms, reports and controls.

If a user opens the database window, they see NO tables. The only
way to get to data is through my forms, where I can control things.

So what I'm saying is that if it were me, and if I had the time, I'd
rebuild it with ADO recordsets. I don't believe ADPs are near death,
but I'm not exactly connected with the Microsoft Product Team.

As for the deployment ofSQLExpress, I'm sure there is a way to do
it, but for the life of me, I couldn't find another newsgroup post. My
article about deploying MSDE is out of date, but roughly what you
need. I built a VB App that managed the installation, but there's got
to be a cleaner way to do that. I just don't know what it is.
--
Danny J. Lesandrini
(e-mail address removed)

Danny thank you for your reply.

Concerning to ADP, I've a large project with 100 customers
installation, so I know the hard question is to rewrite or not with
ADO code. I also use unbound mode so I have a lot of code in my
application.

I'm very suprised that Microsoft said to prefer oldstyle ODBCaccess
toSQLwith Access2007, could it be a preview of ADP's dead?

In order to deploySQLdb i need this feature to be automatic (if
possible) because i have many customers far away from my company.

Rob









- Mostra testo citato -

Hi Danny,
just another question.
If i decide to reduce code rewriting in ADO syntax and so prefer DAO
+ODBC strategy, Can I realize a client/server configuration?
If I execute a query from my access 2007 app Can sql server give me
back only the result records? Or it give me back the entire cursor and
my DAO engine have to filter client-side? as u can notice i'm
considering even performance in my choice

Thank you in advance
Rob

 

[Armen Stein]

If i decide to reduce code rewriting in ADO syntax and so prefer DAO
+ODBC strategy, Can I realize a client/server configuration?
If I execute a query from my access 2007 app Can sql server give me
back only the result records? Or it give me back the entire cursor and
my DAO engine have to filter client-side? as u can notice i'm
considering even performance in my choice

Hi Rob,

As others have said, it depends on whether you utilize some known
techniques to push as much processing as possible to SQL Server. We've
had very good results with this. We usually use ADO only for calling
stored procedures using command objects. Everything else is DAO,
linked tables and passthroughs.

I've written a slideshow on techniques for using Access as a
client-server front-end to SQL Server databases. It's called "Best of
Both Worlds" at www.JStreetTech.com/Downloads.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com