ODBC security / Access to SQL

[Guest]

We have a third party that houses our database. In order to run any reports,
it is necessary to export the data from the SQL DB and then import it into
Access (Daily). Very cumbersum at best. If we were to connect directly into
the SQL DB from Access, would we just have 'read only' ability or would
anyone connected in be able to actually change the raw data through Access?

This is a concern of management before giving any consideration to an ODBC
connection.

Side question: Does anyone just happen to know how many records a SQL DB
can hold.

 

['69 Camaro]

Hi, Gina.

In order to run any reports,
it is necessary to export the data from the SQL DB

SQL DB? Ah. Sybase SQL Server, the name of the relational database before
Microsoft and Ashton-Tate ported it to Windows NT and Sybase renamed its
version to Adaptive Server Enterprise.

Just kidding. You mean _any_ relational database that uses SQL, since you
would have named the product and the version you were using if you wanted a
correct answer that applies to your situation.

and then import it into
Access (Daily). Very cumbersum at best.

Most relational database engines have built-in utilities that make this as
easy as snapping your fingers. If I knew which database engine you have, I
could probably suggest the names of the utilities or techniques you should
be using instead your cumbersome method.

If we were to connect directly into
the SQL DB from Access, would we just have 'read only' ability or would
anyone connected in be able to actually change the raw data through
Access?

It depends upon the permissions granted to the users by the DBA.
Permissions can be as restrictive or as generous as necessary. Also, some
drivers for ODBC connections allow for "read-only" access, such as Jet does,
so you may be able to limit this on your end with a DSN setting, if that's
what you're looking for.

Does anyone just happen to know how many records a SQL DB
can hold.

It only holds one record, if it's a really big record. In other words, it's
the size that counts, not how many times you try.

Which database engine are you using (and version or edition), what's the
storage capacity of the hard drives, and how large can the files be on the
partitions holding the data files, indexes, and log files? If you're
wondering about maximum capacity and you are using Microsoft's SQL Server or
Oracle's non-express editions, non-personal editions, or non-Standard
edition, then if the hard drives and files sizes could handle it, the
database could store several terabytes of data. That's probably more than
enough. On the other hand, the free express editions will hold up to four
Gigabytes of data, which is a suitable capacity for the most common database
applications.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Thank you for your thorough response. You provided me with just the
information I needed. Additional details below.

The application is People Soft. Although we can run queries in People Soft
it takes forever and day to run them. However, when we export the data and
then import it into ACCESS, we can run reports very easily. But again, we
are back to this daily export/import routine. I didn't see any reason why we
could not just connect directly into the DB through ACCESS using an ODBC
connection. However, as mentioned earlier, Managements concern was that
there would be no security. I thought you should be able to put security in
place, but I'm not the DBA. But as an Analyst, I'm used to just having the
data at my fingertips.

 

[Guest]

I just looked up People Soft on the internet. It looks like People Soft is
not SQL based as I had thought. It looks like its Oracle based. Sorry for
the misconception.

 

['69 Camaro]

Hi, Gina.

PeopleSoft requires a relational database engine and can be supported by
Oracle, MS SQL Server, Sybase, DB2, Informix, and SQLBase, so you just need
to find out which one your organization is using. Talk to your DBA about
getting connected securely. That means _never_ storing the User ID and
password in an Access database to "make it easier on the user," as so many
Access developers think is a good idea. Access databases are the first
place hackers look, and find, login credentials for otherwise secure
client/server databases.

However, with a user DSN set up on each workstation, Access can securely
connect to the database engine as long as the user supplies valid login
credentials to authenticate once per session.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Very - very - very - very helpful.

Thank you much.

 

['69 Camaro]

Hi, Gina.

The application is People Soft. Although we can run queries in People
Soft
it takes forever and day to run them.

If you're using PeopleSoft, then it's powered by an enterprise level
relational database engine, which means that if you can get an expert DBA to
work with the query designers, those queries will scream.

However, when we export the data and
then import it into ACCESS, we can run reports very easily.

To be honest, I think you should be working with your DBA to optimize the
queries so that they run in PeopleSoft, not in Access with a copy of the
data. This is your DBA's area of expertise, and it's what your
organization pays your DBA the big bucks to do.

I didn't see any reason why we
could not just connect directly into the DB through ACCESS using an ODBC
connection. However, as mentioned earlier, Managements concern was that
there would be no security.

You can -- with a connection that requires the user to authenticate at the
beginning of each session because the login credentials aren't stored in
Access, as I described in one of my previous posts. However, only do this
as a last resort if your DBA can't work with you to optimize the queries
from within PeopleSoft.

Good luck.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.