vba and adjasent technologies!

amock · Post by **amock** » 09 Nov 2005, 10:51

Hey all,

I recently majored in IS at UCT and have been contracted over the next few months to alter an existing excel/vba application. Up until now I’ve been developing in vb.net, using sql and access database systems. This said I have a few questions in regard to the
use of vba and adjacent technologies:

(1) The current application uses an access DBMS, would i be able to use sql DBMS in vba?

(2) If I were to remain with access, could it handle about 50 users accessing a single tabled access database on a fairly slow intranet? (Users would only access and update about 1 record a day).

(3) Would SQL be able to handle +/- 2000 users accessing a sql database with a similar application to the one mentioned above?

(4) If I were to create a mySQL database, would the vba code be any different to the standard sql code in vba?

Thanks for any advice in advance!

Sojourn · Post by **Sojourn** » 09 Nov 2005, 11:10

1) Dont know.
2) Yes it will manage, but what if that user base grows? What if they start using it more frequintly with more updates to more records? Keep an eye on possible expansion.
3) SQL would be able to handle that very happlily - just make sure the hardware can handle it.
4) Dont know.

s

amock · Post by **amock** » 09 Nov 2005, 14:37

shot for the responce Sojourn, the amount of users for the first system is going 2 stay fairly constant so i think it's a good idea to maintain the current access system.

do have any idea where i can look for prices for sql (for use over intranet&VPN as apposed to internet based)? --> cape town preferably.

i've had a talk with my companies hardware + software supplier (axiz) and they don't deal with sql.

Sojourn · Post by **Sojourn** » 09 Nov 2005, 14:51

amock wrote:shot for the responce Sojourn, the amount of users for the first system is going 2 stay fairly constant so i think it's a good idea to maintain the current access system.

do have any idea where i can look for prices for sql (for use over intranet&VPN as apposed to internet based)? --> cape town preferably.

i've had a talk with my companies hardware + software supplier (axiz) and they don't deal with sql.

1) After some thought and advice I can answer with ease that the two are compatible and you will be able to use sql DBMS in vba.

do have any idea where i can look for prices for sql (for use over intranet&VPN as apposed to internet based)? --> cape town preferably.

You mean a hosting co ? My best advice would be to use IS.
Hetzner is not bad either.

s

amock · Post by **amock** » 09 Nov 2005, 15:37

no, i don't mean the hosting cost, there is no need to host cause it's on an intranet and not the web (hosted on their server for their intranet). what i meant was the actual cost of using sql. the liscensing of the software with microsoft. same as u would purchase a copy of windows or office. i remember a few years ago it was about R10 000 for an intranet version.

Sojourn · Post by **Sojourn** » 09 Nov 2005, 15:54

amock wrote:no, i don't mean the hosting cost, there is no need to host cause it's on an intranet and not the web (hosted on their server for their intranet). what i meant was the actual cost of using sql. the liscensing of the software with microsoft. same as u would purchase a copy of windows or office. i remember a few years ago it was about R10 000 for an intranet version.

ah... I see. You want the licensing prices. You would have to contact vendors directly for that. I have NO idea.

s

amock · Post by **amock** » 09 Nov 2005, 16:00

shot Sojourn,

anyone else got any idea about licensing prices for SQL?

Post by **Kronos** » 09 Nov 2005, 16:17

Firstly, SQL is unconditionally defined as "Structured Query Language".
It is a database querying language technology, very much the same concept as C++. It's a language, and many companies have their own versions of this technology, but they remain consistant.
For Example, a basic Select statement will work on any SQL driven DB no matter what flavour.

I assume that you're talking about Microsoft's version, which is know as "Microsoft SQL Server".

Just talking about "sql" SHOULD refer to the actual language of SQL, but if you try and use it to define a DBMS, it could refer to any of the different flavours of SQL DBMSs (MySQL, MS SQL Server, Oracle, Postgre, Etc, etc)

Just a thought, use it, don't use it...

MS SQL Server is priced in different ways. Per CPU, per User, per server.
The only way to find out for sure is to call MS SA and speak to someone there.

As for your other questions:
1.)VBA (Visual Basic for Applications) doesn't care which DBMS you use. It uses data connectors that connect to any DBMS as long as there is an ODBC Datasource for it. And there are drivers for all the major DBMSs

2.)The Question shouldn't be "Could it handle...?" but rather "How efficiently will it handle it?"
And you've basically already answered that yourself.
"A fairly slow intranet", gives me a clue as to what the rest of the hardware is like "Fairly Slow".
Add to that a possibility of 50 concurrent connections, and you have the N1 between Pta and Jhb in morning rush hour on a Monday!

Crawling!

However, there is very little actual traffic, if each user just works on 1 record a day, but that also depends on how the connections are handled.
Whether they are closed after the data is loaded to the client, and then reopened to update, or if the connection is open all day for each user. Again, the latter will slow it down immensely.

3.) You're putting too many variables into 1 question

a. MS SQL Server can easily handle 2000 users.
b. It will be much more efficient than Access on the same application.
c. You're "Fairly Slow" intranet will have a greater effect when there are 2000 users as apposed to 50.

4.)No (and sometimes yes). The code remains the same as long as you use standard SQL (see explination above).
However, if you use specifics, like built in functions, stored procedures, and advanced DBMS features then it will change somewhat.
Each DBMS has specific funtions and advanced features that work differently to other DBMSs, so your SQL code might change, but your program logic and business code should stay more or less the same. You'll just use a different ODBC adapter for the other DBMS.

amock · Post by **amock** » 09 Nov 2005, 17:44

shot for the info Kronos, i did know what the accronym SQL stood for and i was refering to ms sql server but was just being lazy/inconsiderate when refering to it as sql cause its the only 'flavour' i've used.

in refernce 2 question 2), i think i'm going to do some quick development (RAD --> i love these terms they taught us at varcity) and test how the system works with access, if not it's ms sql server.

and in ref 2 question 4); think i'm gonna develop in ms sql server then maybe change to mysql. I know mySQL is opensource and hence a lot cheaper.

thanks Kronos, very helpful.