Upsizing Access Database

[Chris Burnette]

I am trying to upsize an Access database with the intent of using Access as
the front-end and MSDE as the back-end. I know MSDE allows you to import
DTS
packages, but I am not really familiar with DTS, so I decided to use the
Access wizard.

All in all, it seems pretty straight forward. I chose a default instance of
SQL Server that I created using the MSDE (residing on my local machine), set
a username and password, and told it to link the tables (I also tried using
..adp, but I got the same errors). I also told it to upsize the indexes, but
nothing else.

The program seemed to be working fine, but when it finished it gave me the
following error messages:

EXEC sp_addextendedproperty N'Attributes', N'2', N'user', N'dbo', N'table',
N'EntireSpreadsheet', N'column', N'ID'
EXEC sp_addextendedproperty N'CollatingOrder', N'1033', N'user', N'dbo',
N'table', N'EntireSpreadsheet', N'column', N'ID'
EXEC sp_addextendedproperty N'Type', N'10', N'user', N'dbo', N'table',
N'EntireSpreadsheet', N'column', N'ID'
....

and also:

Server Error 15233: Property cannot be added. Property 'Attributes' already
exists for 'dbo.EntireSpreadsheet.ID'.

I am not entirely sure what these errors mean, and all the data seems to be
displaying properly, but if anyone could offer any suggestions about how to
import the file (even if it's using DTS) I would appreciate it.
Thanks,

Chris

 

[david epsom dot com dot au]

Extended Properties are used for ADP's. Are you going
to use an ADP FE or an MDB/MDE FE? If you are using an
MDB, the extended properties are stored in the MDB anyway.
If you aren't using ADP's, I wouldn't worry about it.

(david)

 

[Guest]

Hmmm ADP vs. MDB, good question. I was originally thinking MDB, but then
someone suggested checking out ADP. I took a look at it, and it does seem
like it does things much more like SQL Server than a regular .MDB, which
would probably make it a better choice at this stage of development.

I'm not really devoted to one or the other, but I want to choose the
technology that will be most advantageous in the long run (i.e. the most
elegant solution). If it makes any difference, we are considering upgrading
to SQL Server 2005 Express when it actually comes out.

Can you make any suggestions as to which would be better? If I do choose to
go with .ADP, are the errors going to be an issue?

Any suggestions would be appreciated.

 

[david epsom dot com dot au]

ADP's are /different/ to MDB's. The security
model for ADP's is broken, but apart from that
people seem to love them and hate them.

I've heard that ADP's won't work with SQL Server
Express. It may be a dead-end technology. Perhaps
you could ask in .adp.sqlserver ?

The extended properties are used to store all
kinds of things for ADP's. I would expect it to
matter for the objects where you have errors.

(david)

 

[Guest]

Thanks for the reply.

I'm curious though, what do you mean that the security model for .adp's is
broken? I was under the assumption that you used MSDE/SQL Server to manage
security when using .adp's. Does this not work properly given the current
state of .adp's?

-Chris

 

[david epsom dot com dot au]

In a perfect world, your application would
be able to do things that your user couldn't
-- authorise payments, look at private data
etc.

Access/Jet supports this through RWOP queries.

SQL Server supports this through 'role based'
security.

Access doesn't support role based security.

It's not the security (Windows/SQL Server) that
is broken - it's the model used by Access

(david)