Upsizing Access

General Web Coding Microsoft Development
1

Hi

i have an application thats Access based.

i know that i can create an SQL db and user on the CP and then upsize (i think?) from within Access. correct>

my questions is whether i can then continue using the ASP coded application, without having to make any coding changes.

thanks!

2

the best way of upsizing it to up size the database on a local maxhine, then sending the .bak to your webspace and get jodo to restore it,

the reason is that the transfer can become slow and fail/cancel out.

it is doable to do a straight tranfer but bare the above in mind.

the only thing you will have to change is where the queryies say True or False , for field values ie WHERE table.field = False

they must be 1 = True and 0 = False

so WHERE table.field = False = WHERE table.field = 0

3

[QUOTE=Emagine]
the best way of upsizing it to up size the database on a local maxhine, then sending the .bak to your webspace and get jodo to restore it,

the reason is that the transfer can become slow and fail/cancel out.

it is doable to do a straight tranfer but bare the above in mind.

the only thing you will have to change is where the queryies say True or False , for field values ie WHERE table.field = False

they must be 1 = True and 0 = False

so WHERE table.field = False = WHERE table.field = 0
[/QUOTE]

Thanks, E.

does this mean that i need SQL server on my local? and have the DB made before too?
(all i have is an expired 3 mnth trial version of 2000 - think it will work?)

thanks!

4

i dont think it will as you have to have the SQL server running.

if you have visual basic enterprise eddition you can get MSSQL 2005 enterprise edition that is fully working to my knowlege

the other way i have do it before is

-open the .mdb with access

  • then set a dsn to MSSQLx (change x for your MSSQL server)
  • then this part takes a while.. export each table to the dsn making sure it creates existing identities, etc.

then you should be ok.. the .bak is the easest.

when using the upsize wizard you dont have to create tables on the SQL server as they are already done during the process.

if you need a hand add aaronhoc [AT] hotmail.com toi MSN or the_dj_air[AT] Yahoo.co.uk to yahoo Messenger

5

Thanks again!
will report back shortly

6

You could also use the MSDE version, it should work for you.

7

[QUOTE=Stephen]
You could also use the MSDE version, it should work for you.
[/QUOTE]

mmm… excuse my ignorance, but whats the diff between MSDE and full SQL?

8

i think its only that MSDE is cut down version, cant hndle asmany connections and can’t do certain things ..
not sure exactly. but mainly classified as cut down.

9

MSDE has a 2GB db size limit and max of 25 concurrent queries.

10

ok, thanks guys

11

[QUOTE=snooper]
Hi

my questions is whether i can then continue using the ASP coded application, without having to make any coding changes.

thanks!
[/QUOTE]

Other than the obvious of changing the connection string in code or in ODBC, there aren’t much changes. Only things I can think of are data types such as

boolean → bit
char → char, vchar or nvarchar
ole → blobs.

Hope it helps.

12

Thanks Mave.

also -

[QUOTE=Emagine]

the only thing you will have to change is where the queryies say True or False , for field values ie WHERE table.field = False

they must be 1 = True and 0 = False

so WHERE table.field = False = WHERE table.field = 0
[/QUOTE]

if i have a field in Access that is a “Yes/No” with a “True/False” format - would that have to be changed, in the queries too?

thanks

13

That would translate to a BIT field. And yes, True and False would need to change to 1 and 0.

If you do something like “SET BooleanField = NOT BooleanField”, you’d have to change it to “SET BooleanField = ~BooleanField”.

14

ok, thanks.

so then, after i have read the feilds from the DB, it would be

if x = 1 then 'was: if x = true then
…
end if

and -

True

would become -

True

yes?

thanks!

15

if you CBool the 1 in the first example x =1 then you can use true ,

for example 2

if the answer is going straight into a database in a UPDATE SQL ie not though a recordset then yes it needs to be a 1 if throuh a recordset using .update you can use true/false

ie using .fields(“fielsd”) = true
.update

is ok

16

Thanks!