Access insert queries

[dominicall]

Don't normally use Access - am an SQL man myself but client doesn't want to pay the extra for SQL hosting - strange???

Anyway - here's the questions...

In SQL I virtually always use Stored Procedures for database work, moving the work to the database. I know I can do the same with Access with queries - although the performance gain isn't quite so great - but it keeps code cleaner too :)

Have a couple of questions re: insert queries...

1. Is query performance improved by declaring the parameters at the beginning of the query, as below:PARAMETERS [@Name] Text ( 255 ), [@Surname] Text ( 255 ),[@MyNumber] Number, [@MyDate] DateTime;2. If declaring the parameters this way, when I'm using a memo field type (for storing long text values) should I just use Text in the parameter declaration??? I tried using either memo or longtext and both gave syntax errors. Does anyone know of a complete listing anywhere of Access query parameter declaration data types - have already looked on MSDN...

Thanks

dominicall

[raf]

Excuse me? This is all completely new and unclear to me.

Why?cause i'm not the best coder in the world + just awake + access is for the real small stuf. So i just use my query's quick and short. I've been doing some checking in my first app's and the processing time was always under 0.05 seconds.

Since you can use access almost only in an almost-single environnement (can it go higher then 20 simultanius users ?) i don't think that the performance would be greatly influenced by "by declaring the parameters at the beginning of the query", whatever that is.
I know parameter-querys from building access forms (but it's just a query that asks for the user input (the parametervalue) in a dialog box)


About your second question. What exactly do you mean with that? here's a listing of ANSI and corresponding Jet datatypes and there synonims

[dominicall]

Always try to avoid using Access but since client won't pay for SQL hosting have to do it in Access...

Am trying to do everything in Queries so when client realises the error of his ways when his site keeps crashing I can just do a database upsize, write the stored procedures and all I'll have to do is change the connection string in my connection include.

Have done a bit of testing and it doesn't seem to make much difference - only about 5% (ish) in performance... but seem to have got it going OK....

Why do clients always ignore 'best advice'... I looked at his traffic stats and told him that he may experience some site/database crashing based on using Access with that many visitors - but he said he didn't want to pay the extra £20/month for SQL hosting...

His money I guess... but he'll be back for SQL upgrade soon enough I'm sure - LOL ;)

Anyway - off to the golf course while the sun is shining :D

dominicall

[raf]

he probably thinks you're ripping him off (based on past experiences) Or he heard that "Internet is great ! And everything is free !" or "quality" is an unknows concept for him :D

of coarse, you can always limit the number of visiters to the app (by keeping a counter in an application value and checking for that in your global.asa) and keeping out all the new clients
--> and of coarse; keeping a log of these turned-down-request so you can wrub his face into the huge quantitys of visitors he missed :)

[dominicall]

Think I've got him coming round.... did a performance comparison test and showed what would happen to his site once he got more than 30 visitors with a stress test....

.... strangely, he seems a bit more interested in listening to me now... I wonder why - LOL

dominicall

[raf]

it's sad, but terror rules.

well, lets look in the mirror and wonder what we can do to make it a better world.

[dominicall]

Play golf while the sun is shining - LOL

:D

[raf]

Thanks for reminding me there's a world outside my machine. Gone of to take a walk.
"Golf = a perfect walk spoiled":D

[dominicall]

Yep - but it's another sunny day so I may well be spoiling another good walk again today :D

It's a tough job, but someone's gotta do it...