Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #9339 > unrolled thread

SQL Backend and other questions..

Back to article view | Back to comp.databases.ms-access

Contents

  SQL Backend and other questions.. musicloverlch <lhowey@gmail.com> - 2014-05-06 09:06 -0700
    Re: SQL Backend and other questions.. bobh <vulcaned@yahoo.com> - 2014-05-06 10:58 -0700
      Re: SQL Backend and other questions.. musicloverlch <lhowey@gmail.com> - 2014-05-06 14:14 -0700
        Re: SQL Backend and other questions.. DFS <nospam@dfs.com> - 2014-05-06 17:38 -0400
    Re: SQL Backend and other questions.. "Albert D. Kallal" <PleaseNOSpamkallal@msn.com> - 2014-05-06 15:54 -0600
      Re: SQL Backend and other questions.. Larry Linson <larry.linson@verizon.net> - 2014-05-19 21:50 -0500
        Re: SQL Backend and other questions.. DFS <nospam@dfs.com> - 2014-05-26 23:36 -0400
          Re: SQL Backend and other questions.. Larry Linson <larry.linson@verizon.net> - 2014-05-27 00:30 -0500
    Re: SQL Backend and other questions.. DFS <nospam@dfs.com> - 2014-05-06 19:46 -0400

#9339 — SQL Backend and other questions..

I have a database split into a front end and back end and I keep reading that it is better if the back end is in SQL.  Really?  Why? Is there a noticeable speed difference? That's the only reason I could think to switch.  I'm being pressured by mgmt to move the database out of Access and into something "more robust". Has anyone tried the MS Access tools in SharePoint?

What other mid-range database tools are there out there?

Any comments would be appreciated.

Thanks,
Laura

[toc] | [next] | [standalone]

#9340

On Tuesday, May 6, 2014 12:06:06 PM UTC-4, musicloverlch wrote:
> I have a database split into a front end and back end and I keep reading that it is better if the back end is in SQL. Really? Why? Is there a noticeable speed difference? That's the only reason I could think to switch. I'm being pressured by mgmt to move the database out of Access and into something "more robust". Has anyone tried the MS Access tools in SharePoint? What other mid-range database tools are there out there? Any comments would be appreciated. Thanks, Laura

To get performance from a SQL back-end there would be several things you would want to do. One being you would want to write pass-thru querys. Another would be if you are doing updates to your data then those SQLSever tables need to have a PK. Indexing is also important but there is a limit where you could have too much indexing on a table.

"more robust" is subjective for me, I find in most cases to date my 'well-written'Access application run just super fine with an Access back-end. I have a few Access applications where an SQLServer back-end was needed but that is just because of the size that the data was going to grow to. I have one SQLServer back-end that is very near 10gig right now and still growing.
hope this helps.
bobh.

[toc] | [prev] | [next] | [standalone]

#9341

I have 3 backend databases that are each about 1 GB each. Is that big enough to warrant a change to SQL?

Thanks for you response.
Laura


On Tuesday, May 6, 2014 12:58:35 PM UTC-5, bobh wrote:
> On Tuesday, May 6, 2014 12:06:06 PM UTC-4, musicloverlch wrote:
> 
> > I have a database split into a front end and back end and I keep reading that it is better if the back end is in SQL. Really? Why? Is there a noticeable speed difference? That's the only reason I could think to switch. I'm being pressured by mgmt to move the database out of Access and into something "more robust". Has anyone tried the MS Access tools in SharePoint? What other mid-range database tools are there out there? Any comments would be appreciated. Thanks, Laura
> 
> 
> 
> To get performance from a SQL back-end there would be several things you would want to do. One being you would want to write pass-thru querys. Another would be if you are doing updates to your data then those SQLSever tables need to have a PK. Indexing is also important but there is a limit where you could have too much indexing on a table.
> 
> 
> 
> "more robust" is subjective for me, I find in most cases to date my 'well-written'Access application run just super fine with an Access back-end. I have a few Access applications where an SQLServer back-end was needed but that is just because of the size that the data was going to grow to. I have one SQLServer back-end that is very near 10gig right now and still growing.
> 
> hope this helps.
> 
> bobh.

[toc] | [prev] | [next] | [standalone]

#9344

On 05/06/2014 05:14 PM, musicloverlch wrote:
> I have 3 backend databases that are each about 1 GB each. Is that big enough to warrant a change to SQL?


Have you repaired/compacted them lately?

If not, they'll shrink drastically.



> Thanks for you response.
> Laura
>
>
> On Tuesday, May 6, 2014 12:58:35 PM UTC-5, bobh wrote:
>> On Tuesday, May 6, 2014 12:06:06 PM UTC-4, musicloverlch wrote:
>>
>>> I have a database split into a front end and back end and I keep reading that it is better if the back end is in SQL. Really? Why? Is there a noticeable speed difference? That's the only reason I could think to switch. I'm being pressured by mgmt to move the database out of Access and into something "more robust". Has anyone tried the MS Access tools in SharePoint? What other mid-range database tools are there out there? Any comments would be appreciated. Thanks, Laura
>>
>>
>>
>> To get performance from a SQL back-end there would be several things you would want to do. One being you would want to write pass-thru querys. Another would be if you are doing updates to your data then those SQLSever tables need to have a PK. Indexing is also important but there is a limit where you could have too much indexing on a table.
>>
>>
>>
>> "more robust" is subjective for me, I find in most cases to date my 'well-written'Access application run just super fine with an Access back-end. I have a few Access applications where an SQLServer back-end was needed but that is just because of the size that the data was going to grow to. I have one SQLServer back-end that is very near 10gig right now and still growing.
>>
>> hope this helps.
>>
>> bobh.
>

[toc] | [prev] | [next] | [standalone]

#9345

"musicloverlch"  wrote in message 
news:aeabbcda-3493-4d4f-9b57-a9abc2d723d0@googlegroups.com...

>I have a database split into a front end and back end and I keep reading 
>that it is better if the back end is in SQL.  Really?  Why? Is there a 
>noticeable speed difference?

The answer is "usually" yes, but ONLY if you do some extra work. In other 
words you could move (generally called up-sizing) the back end to SQL server 
and then link your front end to that SQL database.

For the "most" part your application will work, but MANY things can actually 
run slower. So this is not a "magic" bullet.

> I'm being pressured by mgmt to move the database out of Access and into 
> something "more robust".

SQL server is certainly more reliable. And you can even reliable do a back 
up if someone by accident left their front end open. Such setups also tend 
to be more reliable because by the time one starts using SQL server is often 
about the same time they have some IT support group running and taking care 
of such a server.

And "often" the suggestion to move to SQL server is misunderstand as that 
suggests you not be using Access anymore, but of course Access "remains" the 
front end. So in your discussions MAKE sure you distinguish between the 
Access database system (we call that JET or NOW ACE) as opposed to the 
"developer" tool called Access in which you build forms + reports. Many who 
are new to database systems hear the word "access" and then read stories 
about how it not secure or reliable and thus one needs to use SQL server. 
However do the SAME people realize that you going to continue to use Access 
for the front end? (often they do not).

So using SQL "can" be better and is "usually" better. However just the "act" 
of moving data to SQL server and linking the tables to SQL server is the 
START of the work. Many forms and many features right after this switch to 
SQL server will NOT run faster. You also need to modify most of your VBA 
openReocrdSet commands.  And in forms code that requires use of the auto 
number ID, that number is NOT available with SQL back ends until the 
record/form data is saved (so again this is a potential area of code needing 
to be modified).

>Has anyone tried the MS Access tools in SharePoint?

You can consider moving your back end to SharePoint in place of SQL server. 
I would require a rather "long" post to discuss these issues, but in 
"general" it better to use SQL server for the back end as opposed to 
SharePoint. However I have a number of clients using SharePoint (or low cost 
office 365) SharePoint "lists" and in these cases the SharePoint back end is 
a BETTER choice then SQL server because of the poor and intermitting 
connections and that of using the back end OVER the internet which 
represents a HUGE new set of challenges.

>What other mid-range database tools are there out there?

Do you mean for front end development or do you mean the back end database 
system? As I stated we often say "access" and interchangeable mean the front 
end development tool or do we mean the back end database engine? Often the 
distinction is NOT important, but taking about database tools, then this 
distinction becomes VERY important.

For the "back end" database system?
MySQL- MySQL is very popular and is open source. However the corporate 
licensing as of late is actually more stringent then the free editions of 
SQL server last time I looked.
PostGressSQL - again open source and considered the most feature rich

Oracle - if you have it on site already then a great choice - but it 
expensive to buy + run

SQL server - I tend to recommend this one for Access due to more tools, 
articles and better support in Access.

SharePoint back ends. - This is a really  easy option, and little server 
side skills are required. However this setup only works for smaller tables 
(say below 20,000 records) and does give one a "off line" ability   in which 
the application continues to run without a internet (connection) to the back 
end tables (called lists in SharePoint).


So when you say "database" tools, are you taking about the database system, 
or the tools used to create the software?

If you choose SQL server for the database, then your front end can be:

MS Access
VB6
vb.net
Web based front end.
c++
etc etc. etc.

So always keep in mind the separation of the database system as opposed to 
say building a front end as web based using perhaps visual stuiod .net or 
Access.

Best regards,
-- 
Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
PleaseNoSpam_kallal@msn.com 

[toc] | [prev] | [next] | [standalone]

#9418

On Tue, 6 May 2014 15:54:44 -0600, "Albert D. Kallal"
<PleaseNOSpamkallal@msn.com> wrote:

>"musicloverlch"  wrote in message 
>news:aeabbcda-3493-4d4f-9b57-a9abc2d723d0@googlegroups.com...

 >The answer is "usually" yes, but ONLY
 > if you do some extra work. In other 
 > words you could move (generally called
 > up-sizing) the back end to SQL server 
 > and then link your front end to that
 > SQL database.

I say "Amen" to what Albert wrote, and I'd add the following:

If you use a server db back-end, unless you have IT support who handle
it, realize you are taking on a new career in addition to the one you
now have -- server database administrator. All server databases
require more attention (TLC) than Access.

If your management  chooses Oracle for you, if they do not already
have an Oracle installation and a support staff, you should seek
employment elsewhere unless they offer to send you off to Oracle's
training (neither easy nor brief) so you can handle the new career to
which you are changing because you won't be able to do both.

From what I hear PostgreSQL is also large and complex, but more truly
relational than MySQL.

i have never personally known anyone who got a perfornamce  benefit
from moving to a server database without doing a substantial amount of
redesign and restructuring of the Access frony end. And, even so,
usually not unless their database was very large.

Generally the benefits are in reliability and recoverability rather
than performance for any but very, very large datatabases. I once did
some work on a database with only a few thousand records, set up with
an SQL Server backend... the data was just so urgent, immediate, and
important that it had to be immediately recoverable in case of
emergency -- the firm did stock options trading (and, I was told, did
quite well because they had a genius on the subject in-house).

 Larry Linson
 Microsoft Access MVP 2003 - 2014
 Co-author, Microsoft Access Small Business Solutions, Wiley, 2010

[toc] | [prev] | [next] | [standalone]

#9428

On 05/19/2014 10:50 PM, Larry Linson wrote:
> On Tue, 6 May 2014 15:54:44 -0600, "Albert D. Kallal"
> <PleaseNOSpamkallal@msn.com> wrote:
>
>> "musicloverlch"  wrote in message
>> news:aeabbcda-3493-4d4f-9b57-a9abc2d723d0@googlegroups.com...
>
>   >The answer is "usually" yes, but ONLY
>   > if you do some extra work. In other
>   > words you could move (generally called
>   > up-sizing) the back end to SQL server
>   > and then link your front end to that
>   > SQL database.
>
> I say "Amen" to what Albert wrote, and I'd add the following:
>
> If you use a server db back-end, unless you have IT support who handle
> it, realize you are taking on a new career in addition to the one you
> now have -- server database administrator. All server databases
> require more attention (TLC) than Access.
>
> If your management  chooses Oracle for you, if they do not already
> have an Oracle installation and a support staff, you should seek
> employment elsewhere unless they offer to send you off to Oracle's
> training (neither easy nor brief) so you can handle the new career to
> which you are changing because you won't be able to do both.

Or he can install SQL Server Developer Edition at home, and download the 
full Oracle 11g at home, and learn them at night.

It's not that difficult.  I did both, and it paid off tremendously.  Not 
only financially, but in your ability to be more self-sufficient on the 
job.  There's nothing worse than having to beg a self-righteous Oracle 
DBA to drop and recreate a table for you.

Best thing to learn about Oracle is how to administer the database with 
DDL scripts and a little PL/SQL code.

You can run scripts (eg create_environment.sql) to re/create an entire 
Oracle schema: tablespaces, tables, constraints, sequences, 
roles/grants/privileges, users, views, stored_procedures, triggers, etc.

Examples:
create tablespaces (Unix/Linux):
--FAST GROWTH TRANSACTION DATA
  CREATE TABLESPACE XXX_TRANS_DATA

    DATAFILE '/u05/oradata/wkfd/XXX_TRANS_DATA_01.dbf' SIZE 250M,
             '/u05/oradata/wkfd/XXX_TRANS_DATA_02.dbf' SIZE 250M

    DEFAULT STORAGE (
       INITIAL          10M
       NEXT             10M
       MINEXTENTS        1
       MAXEXTENTS  	UNLIMITED
       PCTINCREASE       1
  );


table
CREATE TABLE XXX.TT_ORDER_CUSTOMER_PHONE
(
	CUST_ID		NUMBER(10)	NOT NULL,
	PHONE_NBR_TYPE	VARCHAR2(1)	NOT NULL,
	CUST_PHONE_NBR	VARCHAR2(30)	NOT NULL
)
TABLESPACE XXX_TRANS_DATA
   STORAGE (	INITIAL 1M
		NEXT 1M
		MINEXTENTS 1
		MAXEXTENTS 10000
		PCTINCREASE 0);


ALTER TABLE XXX.TT_ORDER_CUSTOMER_PHONE
	ADD CONSTRAINT PK_TT_ORDER_CUSTOMER_PHONE PRIMARY KEY
	(
		CUST_ID,
	  	PHONE_NBR_TYPE
	);

COMMENT ON TABLE XXX.TT_ORDER_CUSTOMER_PHONE IS 'customer phone numbers';
COMMENT ON COLUMN XXX.TT_ORDER_CUSTOMER_PHONE.OCUST_ID IS 'foreign key 
to Order Customers table';
COMMENT ON COLUMN XXX.TT_ORDER_CUSTOMER_PHONE.PHONE_NBR_TYPE IS 'type of 
phone number R-residence,B-business, etc';
COMMENT ON COLUMN XXX.TT_ORDER_CUSTOMER_PHONE.OCUST_PHONE_NBR IS 'phone 
number';


constraints
ALTER TABLE XXX.TL_UNIT_ID_TICKET_LOC
	ADD CONSTRAINT FK_TL_UNIT_ID_GDS FOREIGN KEY ( GDS_CODE )
	REFERENCES XXX.TL_GDS ( GDS_CODE );

sequences (sort of an autonumber)
CREATE SEQUENCE XXX.ORDER_CUSTOMER_ID_SEQ
  MINVALUE 1
  NOMAXVALUE
  INCREMENT BY 1
  NOCYCLE
  CACHE 200
  ORDER

roles
DROP ROLE ROLE_XXX_REPORT_USER;
DROP ROLE ROLE_XXX_DEV_USER;
CREATE ROLE ROLE_XXX_REPORT_USER;
CREATE ROLE ROLE_XXX_DEV_USER;

grants (privileges on objects)
GRANT SELECT ON XXX.TBLNAME TO ROLE_XXX_REPORT_USER;
GRANT SELECT, INSERT, UPDATE, DELETE ON XXX.TBLNAME TO ROLE_XXX_DEV_USER;

users
DROP USER XXX_ADMIN;
CREATE USER XXX_ADMIN IDENTIFIED BY XXX_ADMIN
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, DBA TO XXX_ADMIN;
GRANT ROLE_XXX_CREATE_SESSION TO XXX_ADMIN;
GRANT ROLE_XXX_ADMIN_USER TO XXX_ADMIN;

DROP USER CHUAN;
CREATE USER CHUAN IDENTIFIED BY CHUAN
   DEFAULT TABLESPACE USERS
   TEMPORARY TABLESPACE TEMP;
GRANT ROLE_XXX_CREATE_SESSION TO CHUAN;
GRANT ROLE_XXX_REPORT_USER TO CHUAN;


views
CREATE OR REPLACE VIEW XML_NO_ORDER
SELECT X.ORDER_ID AS XML_WITH_NO_ORDER
FROM TT_ORDER_XML X, TT_ORDER O
WHERE O.ORDER_ID (+) = X.ORDER_ID
AND O.ORDER_ID IS NULL	

stored procedures
CREATE OR REPLACE PROCEDURE DELETEORDERDELIVERY (DeliveryID NUMBER) IS
BEGIN
   DELETE FROM TX_ORDER_DELIVERY_ITEM WHERE DELIVERY_ID = DeliveryID;
   DELETE FROM TT_ORDER_DELIVERY WHERE DELIVERY_ID = DeliveryID;
   COMMIT;
END DELETEORDERDELIVERY ;

triggers
CREATE OR REPLACE TRIGGER XXX.BI_GET_NEXT_ORDER_ID
   BEFORE INSERT
   ON TT_ORDER
   FOR EACH ROW
BEGIN
  IF :new.ORDER_ID IS NULL THEN
   :new.ORDER_ID := NEXTORDERID;
  END IF;
END;



There.  Now you're 50% Oracle DBA, 100% Access stud.


Also: https://asktom.oracle.com




>  From what I hear PostgreSQL is also large and complex, but more truly
> relational than MySQL.
>
> i have never personally known anyone who got a perfornamce  benefit
> from moving to a server database without doing a substantial amount of
> redesign and restructuring of the Access frony end. And, even so,
> usually not unless their database was very large.
>
> Generally the benefits are in reliability and recoverability rather
> than performance for any but very, very large datatabases. I once did
> some work on a database with only a few thousand records, set up with
> an SQL Server backend... the data was just so urgent, immediate, and
> important that it had to be immediately recoverable in case of
> emergency -- the firm did stock options trading (and, I was told, did
> quite well because they had a genius on the subject in-house).
>
>   Larry Linson
>   Microsoft Access MVP 2003 - 2014
>   Co-author, Microsoft Access Small Business Solutions, Wiley, 2010
>

[toc] | [prev] | [next] | [standalone]

#9429

On Mon, 26 May 2014 23:36:19 -0400, DFS <nospam@dfs.com> wrote:


 > Or he can install SQL Server Developer Edition
 >  at home, and download the 
 > full Oracle 11g at home, and learn them at night.
 >
 > It's not that difficult.  I did both, . . .

I think your approach was commendable, and I never discourage
learning.

If you worked in an environment where the DBA or department that
controlled server databases would grant admin authority to a ' mere
mortal' user, it must be a very different environment that those in
which I worked.

Only once did I have any server DB admin authority (on an Informix DB)
and that only because it was a departmental database under the control
of the prime contractor for whom I wrote, limited to specific
projects.

All my Access work was as a consultant, contractor, or subcontractor,
and I did not have time to "joust with windmills" trying to obtain
admin privileges. I had plenty to occupy my time doing Access. Oh, and
I had plenty of Access work up until the time I (semi-) retired, so
didn't need to expand my skill set to get more work.

I think your example confirms that one takes on significant additional
work by pursuing DBA authority.

I've done some minimal admin work on SQL Server databases at a
personal of workgroup level, and, IMNSHO, it is far easier than
Informix and Informix is far easier than Oracle. My view on Oracle is
on observation and discussions with Oracle developers and DBAs; I am
aware that IBM bought Informix and don't know the current status of
that product.

To me, none of them seemed in the least easy if you are installing a
complex database operation/center with clustering, load balancing,
etc.

 Regards,
 Larry

[toc] | [prev] | [next] | [standalone]

#9346

On 05/06/2014 12:06 PM, musicloverlch wrote:

> I have a database split into a front end and back end and I keep
> reading that it is better if the back end is in SQL.  Really?  Why?
> Is there a noticeable speed difference? That's the only reason I
> could think to switch.  I'm being pressured by mgmt to move the
> database out of Access and into something "more robust".

I like Access, but nearly everything is more robust than the Jet engine.


How many concurrent users will you have?

What row counts do you have in the main tables?

How 'complicated' is your db?  Lots of relationships and indexes?

How much updating/inserting/deleting is being done?


I lost a few records to Access table corruption over the years - there's
NO excuse for that.  Suddenly the columns are filled with gibberish, and
you can't get that data back except thru a backup.



> Has anyone
> tried the MS Access tools in SharePoint?
>
> What other mid-range database tools are there out there?


I've lately been looking at this powerful public domain filesystem db 
called SQLite
www.sqlite.org

There's an ODBC driver available.
http://www.ch-werner.de/sqliteodbc/

I can't say it will work better for you than Access, but it's capacities 
are much higher.

http://www.sqlite.org/limits.html


I've been trying to find someone who replaced a multi-user Access 
backend with SQLite (and ODBC links to the Access frontend), but no luck 
so far.



> Any comments would be appreciated.
>
> Thanks, Laura

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web