Access Project connect to SQL Server

[Guest]

Hi, I've been programming VBA with Access for about a year. I have an Access
DB I've have working fine. I now want to turn it into an Access project and
have attempted to UpSize my .mdb file using the UpSize Wizard to a SQL DB. I
can do this just fine on my local machine (I have SQL Server 2000 Developer)
but when I try to upsize and create a SQL DB on a server that has SQL Server
2000 installed I get errors. Our DBA has given me database create rights.
Do I need SQL Admin. rights to perform this process?

 

[Alex White MCDBA MCSE]

Hi Donald,

Try creating the DB on the server manually then upsizing to that new DB,
after that point you may have problems upsizing data so try to upsize
without any data just structures if you run into that problem.

 

[Van T. Dinh]

Database Creator permission is fine. I doubt the DBA will give you the "sa"
permission: it is *his* domain. Some DBA even creates an empty database for
you and only give you the permissions inside this database so that he/she
doesn't have to give you Database Creator permission.

 

[Guest]

Alex,
Thanks for the reply, that will be my next step. The DBA here is a
super-control freak. She has never developed software before and looks at
programmers kind of like hackers. I did convince her to allow me to have
create rights and she sent me an email telling me, "Be careful and don't
screw up anything on the server!" That's what I'm dealing with so tend to
believe that my problems have to do with rights but maybe not so. I will
figure it out eventually.

Don

 

[Guest]

Van,
Read the reply I sent to Alex. You hit the nail on the head. I have to
beg the DBA here to give me rights to get this accomplished and tend to
believe that my problems are rights related or something she is doing on the
backend that I don't know about. Oh well, I will figure this out eventually.
Thanks for the info. I'll let you know how I solve it.
Don

 

[Guest]

donald,

going from a 'simple' a2k mdb sitting on a LAN/shared drive in my org's
network to moving its tables into SQL server and retaining the a2k as the
front-end is an idea that i've begun toying with. this thread sort of ended
on a suspenseful note. since i want to know what pitfalls to avoid, i was
interested in posting this to you with a view to getting the inside scoop.

-ted

 

[Guest]

Ted,
Please excuse me for not getting back to you sooner, I've been on vac.
fishing.
I am still in the process of the upsizing the database. The upsizing
wizard is a bit confusing at first and I had to go thru a DBA who is a bit of
a control freak and at first couldn't get the wizard to work. I finally got
the database upsized using the wizard without any data in the tables. Then,
imported the data later. Also, the SQL in Access is not totally compatible
SQL Server. If you have SQL code in your Access database that start out as
SELECT DISTINCT ROW, SQL Server won't like it so you'll have to change those
SQL statements. I also wish I had been the original developer on the
project, I'm having to fix a lot of stuff. If you don't have a copy of SQL
Server Developer Edition, I would get a copy before you even start. Also,
pick up a book called Microsoft Access Developer's Guide to SQL Server by
Mary Chipman and Andy Baron. It's a good reference book and has a complete
chapter on Access Projects.
If you have any specific questions just let me know.

Don
----------------

 

[Guest]

hi don,

hope the fish were more obliging than some of the quirky goin's on you
narrated regarding SQL Server.

i see a yellow cautionary light flashing as i ponder whether to xross this
intersection so to speak: i think there may be at least one instance where
'distinct' is used somewhere in my vba/sql coding. i had ordered the book you
spoke of and gotten a copy of the sql developer only software (which for some
reason i seem to have been unable to install on my home desktop xp pro -- i
can try re-installing and recording the exact issues if you're interested).
lastly, i have discovered some antipodean outfit that sell a product they
swear will take out the sweat from doing this sort of thing; i can delve into
my notes and give you their particulars (website and so on) if you like.

-ted