How do I connect to my SQL database using Access 97 or Access 2000?
What software is used for SQL?
What tools do I need?
I have a problem connecting to SQL Server
via Enterprise Manager?
What database scripting technologies can I use with SQL?
What is the maximum size of database I can have?
Will it support Access 2000 ?
Should I use Access or SQL ?
My wildcard search that worked with Access fails with SQL ?
How do I connect to my SQL database using Access 97 or Access 2000?
First, you must create an ODBC data source on your local machine that links to your SQL database.
- Go to Start, Settings, Control Panel, ODBC Data Sources.
- Choose the File Tab and click Add.
- Scroll down to choose SQL Server and then click Next. (Note: If SQL Server is not on the list, you must install the SQL ODBC drivers
before you can proceed. You can do this by re-running setup.exe from the CD that you originally used to install Access. See the Access
Help article 'About installing ODBC drivers' for specific instructions.)
- Type a name for your data source. This can be any name you choose, but to avoid confusion you may want to use the same name as
your database. Click Next, then Finish.
- In the Server field, type the ip address of the SQL server where your database resides. Click Next.
- Choose SQL Server authentication, and type in the SQL user id and password that were created for you.
- Click on Client Configuration and ensure that TCP/IP is chosen as the Network library. Click OK and then Next.
- Change the default database to the name of your SQL database. Click Next.
- Click the box to Use Regional Settings and then Click Finish.
- Click Test Datasource. You should receive a message that says "TESTS COMPLETED SUCCESSFULLY!" Click OK three times.
You can now use the Access client to connect to your SQL database using the ODBC data source created in the previous steps.
To create a new SQL database table, first create a new Access database (or open an existing one) and then use an Access pass-through
query. This sends SQL commands to the SQL database server. Follow the instructions in the Access Help article 'Send commands to an SQL
database using a pass-through query'.
To create an Access database table which links to an existing table in your SQL database, first create a new Access database (or open an
existing one). Then, in the Database window choose New, Link Table, OK. Choose Files of Type, ODBC databases. Highlight the data source
you created in steps above and click OK. Type in your password and click OK. Highlight the table you want to connect to and click OK.
Highlight the fields you want to view and click OK.
Back to Top
What software is used for SQL?
Microsoft's SQL server version 7 service pack 2 is employed. See Microsoft's SQL site for revision information.
Back to Top
What tools do I need?
SQL server ships with client tools which allow data transformation services. Wizards enable upsizing of legacy data such as Microsoft Access
databases. Once up-sized Query analyser and Enterprise manager allow manipulation of data and structures natively.
Other tools such as Microsoft Interdev can be enabled to hook directly into the power of your SQL database.
Back to Top
I have a problem connecting to SQL Server via Enterprise Manager?
Some people experience problems using Microsoft's SQL Server client tools such as the Enterprise Manager to connect to their SQL Server
database. The error that will most commonly occur is:
Unable to Connect To Server (reason: [DB-Library] Unable to Connect: SQL Server is unavailable or does not exist. Specified SQL
Server not found). Register anyway?
There are several problems that may cause this. The first is to make sure that you are using the correct IP address (we suggest using the IP
address to register the server) and have the correct login name and password. If you are unsure of any of the above, please email
support@xhost.co.uk. to confirm the information.
If the above is correct, the problem may exist in your installation of Microsoft's client utilities. A major issue is that the utilities install with the
communication protocol of Named Pipes selected as the default protocol. This must be changed to TCP/IP. To do this:
- Go to your Menu Bar and select the SQL Client Configuration Manager from the SQL Server program group.
- Select the Net Library tab.
- Select TCP/IP as the Default Network
- Click on the Done button.
Back to Top
What database scripting technologies can I use with SQL?
We support Allaire's Cold Fusion, Microsoft's Active Server Pages and Microsoft's Internet
Database Connector scripting languages. There are differences between Microsoft
SQL Server and Microsoft Access query syntax:
| Access | SQL Server |
|---|
| ORDER BY in queries | ORDER BY in views not supported |
| DISTINCTROW | DISTINCT |
| String concatenation with "&" | String concatenation with "+" |
Supported Clauses/Operators:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
WITH OWNERACCESS
Not Supported:
COMPUTE, FOR BROWSE, OPTION |
Supported Clauses/Operators:
SELECT
SELECT TOP N
INTO
FROM
WHERE
GROUP BY
HAVING
UNION (ALL)
ORDER BY
COMPUTE
FOR BROWSE
OPTION
Not Supported:
WITH OWNERACCESS |
Aggregate functions:
AVG
COUNT(column)
COUNT(*)
MIN
MAX
FIRST
LAST
STDEV, STDEVP
SUM
VAR, VARP |
Aggregate functions:
AVG([ALL | DISTINCT] expression)
COUNT([ALL | DISTINCT] expression)
COUNT(*)
GROUPING (column_name)
MAX(expression)
MIN(expression)
STDEV, STDEVP
SUM([ALL | DISTINCT] expression)
VAR, VARP
Not supported:
FIRST, LAST |
TRANSFORM
(SELECT statement)
PIVOT |
WITH ROLLUP, WITH CUBE on SELECT statements |
MAKE TABLE, ALTER TABLE
Supported clauses:
CONSTRAINT
ADD COLUMN
DROP COLUMN
DROP INDEX
Also,
Stand-alone statement:
DROP INDEX |
CREATE TABLE, ALTER TABLE
Supported clauses:
CONSTRAINT
ADD COLUMN
DROP COLUMN
Stand-alone statement:
DROP INDEX |
Back to Top
What is the maximum size of database I can have?
SQL server can accomodate a database of any size. With support for a potential 32
processors and 64 GB of memory the bottlenecks will lie within the web enabling technology
and wide area network.
Back to Top
Will it support Access 2000?
Microsoft Access 2000 can access SQL Server directly, enabling native, transparent
client/server interaction.
Back to Top
Should I use Access or SQL?
That depends. Access is cheap. It is easy to create tables and modify them as needed.
But in a production environment Access has some major drawbacks:
- It is not designed to handle many concurrent writes into the database.
- It is hard to modify an Access database without taking it offline.
- Locking doesn't work as well as in SQL Server
- Using SQL Server constraints and transactions makes it easier to protect the database from logical corruption.
Back to Top
My wildcard search that worked with Access fails with SQL?
This is normally because you can use * and ? as wildcards with Access while you should use %
and _ respectively when using a SQL Server. For instance:
SELECT PhoneNumber WHERE NAME LIKE 'John*'
should be replaced with:
SELECT PhoneNumber WHERE NAME LIKE 'John%'
when using SQL Server. The % and _ also work with Access, so may just as well always use them.
Back to Top
|
