Upgrade 2007 accdb database to SQL server 2005 or 2008

[brian]

Hi

I have an access database driving a very meaty report with about 300
fields from current record.

It uses a word template and the mailmerge facilty.

All is kinda ok but because of the number of fields and lookups I have
had to split the data up into three separate tables linked 1 to 1. I
have also split the template in 3 so it means running the merge three
times (once for each section) to get the merge job done.

Having three seperate forms is ok - thopught about using subforms but
3 forms is quite convenient in this case.

I wish to address this and I am assuming that upgrade to sql server
could be a sensible route however a little unclear on the logic.

Would this enable me to use access as the front end AND use a linked
table to the SQL backend table which contained more than the 255
fields normally allowed in Access.

I am more of an access person than SQL server so tend to keep to what
I know reasonably well. I think I'm not really clear on the using
Access as a front end to SQLServer details and limits - SQLserver
limits or Access limits? number of fields in table or linked table
particularly.

Some code is replicating a dozen or so fields between the tables at
the mo to ensure all fields needed by the merge for that part of the
doc are present in the table.

There are some access memo fields in case that makes a difference.

Any advice appreciated - is upgrade to SQL a good route?

Brian

 

[Rick Brandt]

Hi

I have an access database driving a very meaty report with about 300
fields from current record.

It uses a word template and the mailmerge facilty.

All is kinda ok but because of the number of fields and lookups I have
had to split the data up into three separate tables linked 1 to 1. I
have also split the template in 3 so it means running the merge three
times (once for each section) to get the merge job done.

Having three seperate forms is ok - thopught about using subforms but 3
forms is quite convenient in this case.

I wish to address this and I am assuming that upgrade to sql server
could be a sensible route however a little unclear on the logic.

Would this enable me to use access as the front end AND use a linked
table to the SQL backend table which contained more than the 255 fields
normally allowed in Access.

I am more of an access person than SQL server so tend to keep to what I
know reasonably well. I think I'm not really clear on the using Access
as a front end to SQLServer details and limits - SQLserver limits or
Access limits? number of fields in table or linked table particularly.

Some code is replicating a dozen or so fields between the tables at the
mo to ensure all fields needed by the merge for that part of the doc are
present in the table.

There are some access memo fields in case that makes a difference.

Any advice appreciated - is upgrade to SQL a good route?

Brian

SQL Server will not address your problem. While the server will allow
more than 255 fields in a table, Access will not be able to link to a
table with that many fields so you are back where you started.

What you need to do is fix your table design. There is no way a single
entity needs to be described with that many fields. You more than likely
have one-to-many relationships that ought to be modeled with multiple
tables crammed into one large table. Fix your design and the problems go
away.

 

[brian]

SQL Server will not address your problem.  While the server will allow
more than 255 fields in a table, Access will not be able to link to a
table with that many fields so you are back where you started.

What you need to do is fix your table design.  There is no way a single
entity needs to be described with that many fields.  You more than likely
have one-to-many relationships that ought to be modeled with multiple
tables crammed into one large table.  Fix your design and the problems go
away.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com- Hide quoted text -

- Show quoted text -

Rick

Thanks for the confirmation.

I kind of thought that was the answer.

Although a lot of fields the design more or less meets the
normalisation rules.

If I could change the design a little my understanding is that it is
not so easy with 2007 to get mailmerges going from queries on current
record particularly where memo fields etc are involved?

Breaking down the design would presumably mean running the merge from
a query.

Perhaps automation code is the answer here instead of mailmerge but
presumably that would take a lot more effort.

Would another similar database (instead of Access) where tables hold a
higher number of fields be the answer?

Presumably Access 2010 tables will not have a greater field capacity
per table?

Brian

 

[Sylvain Lafontaine]

If you keep a MDB or ACCDB frontend, then there will be no change at all on
the number of maximum fields. However, if you switch to and ADP project,
then likely this number will be higher. However, I have no idea how this
limit would translate into your word template and mailmerge facility.
You'll have to try it by yourself.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Albert D. Kallal]

Although a lot of fields the design more or less meets the

normalisation rules.

"more or less" is not that defined here. I seen job costing systems in which
you can "choose" the costing system (by weight, by material used, by labor
etc etc etc etc). And, these systems allow you to create any type of
assembly from scratch. These systems can allow you to job cost a jumbo jet
or the space shuttle...and we likely not had a table with more then 50
fields.

If I could change the design a little my understanding is that it is

not so easy with 2007 to get mailmerges going from queries on current
record particularly

I don't see the problem with the above.

where memo fields etc are involved?

That's not 100% clear here. Are you using memo fields now?

Breaking down the design would presumably mean running the merge from

a query.

Yes, the above makes sense.

Perhaps automation code is the answer here instead of mailmerge but

presumably that would take a lot more effort.

Above is not a given based on the info so far.

Would another similar database (instead of Access) where tables hold a

higher number of fields be the answer?

Well, the problem is then will word support more then 255 fields? It is
debatable if word will support more columns for a merge.

Presumably Access 2010 tables will not have a greater field capacity

per table?

Not likely. Been developing databases for about 20 years. I think I had
table with maybe 50 fields once in that time. We talking about oil spill
simulation software, and two payroll systems I written from scratch. And,
then throw in some tour reservation systems that allow every conceivable
type of hotel room layout and seasonal pricing system so complex that you
can't "imagine" it.

So, unless you developing something that the technology industry on the
planet earth never seen, then the only reason why you having trouble here is
because you trying to use a database as a spreadsheet and that's like
putting a round peg into a square hole.

Keep mind, while normalizing you data will eliminate the issue of running
out of fields, there is still the issue of that information being placed
into a word document. Perhaps one considers using a report in ms-access.
Note that ms-access 2007 memo fields do allow rich text support. So, you
could conceivable build a report system in which a text box has "many" memo
fields built from a data table which defines what text is to be in the
report.

If you MUST use word, and it also likely that your un-normalized designs
can't be changed easily, then you could consider using bookmarks. I find
them messy, but it would eliminate restrictions here, and still allow the
use of memo fields. You can search the groups...there are many examples
posted using bookmarks.

 

[Sylvain Lafontaine]

Just made some quick tests using a SQL-Server table with 303 fields as the
backend and an ADP project as the FrontEnd: I was able to add these 303
fields to a single report (no sub-report) if it was without label; however,
by adding both the label and the bound field, I hit a limit at 233 fields;
which mean that the limit for a ADP report would be something like (2 * 233)
= 466 controls.

For a form, I hit the limit at 297 (label + bound text box), for a total of
a maximum of 594 controls on a single form.

So, for an ADP project, the limits are slightly higher than for a MDB or
ACCDB file but not that much higher; nothing close to the limit of 1024
fields per tabld of SQL-Server but still a little higher than the 255 fields
of MDB or ACCDB.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[brian]

Just made some quick tests using aSQL-Server table with 303 fields as the
backend and an ADP project as the FrontEnd: I was able to add these 303
fields to a single report (no sub-report) if it was without label; however,
by adding both the label and the bound field, I hit a limit at 233 fields;
which mean that the limit for a ADP report would be something like (2 * 233)
= 466 controls.

For a form, I hit the limit at 297 (label + bound text box), for a total of
a maximum of 594 controls on a single form.

So, for an ADP project, the limits are slightly higher than for a MDB or
ACCDB file but not that much higher; nothing close to the limit of 1024
fields per tabld ofSQL-Server but still a little higher than the 255 fields
of MDB or ACCDB.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming forAccessandSQL-Server
(French)








- Show quoted text -

Thank you all

and particularly Sylvain for the ADP test.

I didn't know about ADP so googled it on the web but the first thing I
saw was 'The .adp has been deprecated and no further development will
be done with it. It is still supported with A2007 but there is no word
of its future after that.' which puts me off a bit.

Brian

 

[Sylvain Lafontaine]

The futur of ADP is incertain at this moment but quite possibly, its
features will be integrated in a futur version of Access (but I have no
proof of that). It's simply that I don't see how a futur version of Access
could lose all capabilities about the possibility of calling directly a
stored procedure or not to have any possibility of a read/write passthrough
query instead of read only passthrough queries.

In your case, as your problem seems to be mainly with the mail merge, maybe
that by using another method you'll have the possibility of performing the
mail merge into a single operation; see for example:

http://support.microsoft.com/kb/285176

Another possibility would be to simply open an ADO recordset and use
Automation to build your word document.

If you wan to go with SQL-Server in order to merge your three tables into
one but keep ODBC linked tables, you'll have to use views to split your
table in three for the linking. Seel
http://support.microsoft.com/kb/q209123/ to know how to have an updatable
(read/write) linked view. Notice also that in this case, you cannot put a
timestamp in your table.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Just made some quick tests using aSQL-Server table with 303 fields as the
backend and an ADP project as the FrontEnd: I was able to add these 303
fields to a single report (no sub-report) if it was without label;
however,
by adding both the label and the bound field, I hit a limit at 233 fields;
which mean that the limit for a ADP report would be something like (2 *
233)
= 466 controls.

For a form, I hit the limit at 297 (label + bound text box), for a total
of
a maximum of 594 controls on a single form.

So, for an ADP project, the limits are slightly higher than for a MDB or
ACCDB file but not that much higher; nothing close to the limit of 1024
fields per tabld ofSQL-Server but still a little higher than the 255
fields
of MDB or ACCDB.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming forAccessandSQL-Server
(French)








- Show quoted text -

Thank you all

and particularly Sylvain for the ADP test.

I didn't know about ADP so googled it on the web but the first thing I
saw was 'The .adp has been deprecated and no further development will
be done with it. It is still supported with A2007 but there is no word
of its future after that.' which puts me off a bit.

Brian

 

[GBA]

I believe the standard SQL Server table field width spec is 1024 fields and
8000 byte; but there are techniques that can increases these values by
several factors. At least that is what my text book says; you would want to
check with the MS SQL Server site MVPs.

On the otherhand; SQL Server is an entirely different product set - even
the Express version - that is a whole new learning experience. I know others
disagree on this point, but I see very little benefit in attempting to use
any Access type front end if one is going to convert to SQL Server. If you
must go to SQLserver (or MySQL for that matter) plan to go completely...and
use Visual.Net for the front end plus SQL Report Server. I don't think
attempting to stay hybrid with Access is going to lessen your learning curve
or make it easier at all.

Don't want to start a religious war on this though. SQL Server is a very
very comprehensive suite of features. Very good yet complex. just an
opinion.