Welcome to my ASP Code Website

Using Microsoft Access with SQL Server



If you're designing ASP pages to work with a SQL database, you need some way to create and maintain the tables of data. Microsoft Access lets you do that easily.

First off, make sure you get your hands on Access XP or 2000 or above. The older versions of Access weren't really designing to handle the level of SQL web work that goes on nowadays and made this sort of maintenance VERY difficult if not nearly impossible. Also make sure you've upgraded to SP2 to be able to use the SQL tools.

Launch Access and set up a new PROJECT. While a "database" in Access would let you link to (and view) content in your SQL database, by setting up a project you can actually create, edit and delete tables in your SQL Server database.

Put in the information provided by your hosting company about the IP address, username and password for this database. Choose the actual database name from the list provided. You now have a direct connection to that SQL Server database.

The easiest way to create tables is by importing the text file of data. You can export the data from any old system you were using or create a text file from scratch. Have the top row contain the field names. Tabs are usually the best delimiters (field separators) because data often has single or double quotes in it. The sequence of events would then be -

File
Get external data
import
delimited
row field names
new table (name)

that's it! The data imports, you have a table with good field names, and you can start writing ASP code to use that table!

ASP Server Setup Information