XLS to Access to sql, all data skipped by Upsizing Wizard 2

[TexARC]

Very rudimentary problems here...
I used to do db design in relational dbs many years ago, so I was volunteered to do this project for the nat'l archery organization. I'm way over my head. I currently have around 2000 archer records in a simple spreadsheet, built to convert to a (hopefully) relational db.

I have migrated the spreadsheet contents into both a flatfile Access database, as well as a normalized relational database in Access 2003, where consistent values are in related tables("Indoors/Outdoor", "Male/Female", etc.) with ID Keys in all tables. Not a very big database, and one that will not have a lot of changes each year. But it needs to be able to display subsets based on choice fields deriving from fields in the database ("Show me Male Compound Indoor 18meter records in descending order by score")

My goal is to create an online searchable database using Web Studio. It's pretty easy to get going on, but all the tutorials use sql databases, and since this is a learn-as-i-go novice effort, I want to use sql rather than Access.

Using the upsizing wizard creates all of the tables in sql, but no data is transferred- when I view the data using Web studio 2005 nothing but NULL in each field. All field names in access are simple unspaced words ("Archer", "Gender", "Distance", "Round", etc.) and seem to make the trip into the sql database tables. Each record/row has around 12 fields, and I can't afford to sit down and type all of them into the empty sql database that the upsizing wizard created.

The only tips I can find say to forget the wiz and use DTS, but I can't find out how to use DTS since I have SQLexpress 2005, and know next to nothing about sql.

This is likely an incredibly basic and easy task to accomplish, but I can't get the data moved from Access 2005 into an SQL file format.
I have "Microsoft SQL Server 2005" as a folder on my programs list, and SQLExpress 9.0.2047 running as the server instance, but other than configuration tools there is nothing in the folder. Visual Studio lets me create a data connection to "sqlexpress" so I'm reasonably certain that the sql server is running.

Apparently everyone in the world that knows anything about sql doesn't think to explain how to actually use it, where and how to invoke it.

For example, tutorials commonly describe how to use SQL and will tell you "type SELECT "column_name" FROM "table_name"" as an example, but never say where the heck to type this command!(what program?) I've yet to find a getting started tutorial that is as dumbed-down as I am.

My best guess to date is that you have to have another program to act as the front end, such as Access. Yet I can't get Access to upsize the data. major noobie argh.

Anybody willing to help with such rudimentary problems? Will gladly trade help for advanced archery instruction!

High Performance Coach, Level III, USA Archery
webmaster, Texas State Archery Association
(

http://www.texasarchery.org)

 

[gmmastros]

>> My goal is to create an online searchable database using Web Studio.

You better check with your online hosting company to make sure they support sql server database (sql 2005 specifically). Most do, but it would be terribly wasteful to build an app using SQL Server only find out you have to change databases at the last minute.

So... assuming all of that is ok.

You should have a 'sql server management studio' installed on your computer. start -> programs -> Microsoft SQL Server 2005

After starting up the Management Studio, click View _> Object explorer. The data is presented similarly to explorer, where the server will be located at the top. below that, you'll see things like, Databases, Security, server objects, etc... You'll first need to create a database, so right click databases, then click New Database. Give it a database name and click OK.

Now that you have a database, expand the databases section. The new database you created will appear. To import the data... Right click the database, click tasks, click 'Import Data'. At this point, a 'SQL Server Import and Export Wizard' will appear. Follow the prompts to import your data in to this database.

You will also use the management studio to modify tables, create stored procedures, test your queries, etc... Click File -> New -> Query. This is where you will probably spend most of your time.

>> Anybody willing to help with such rudimentary problems? Will gladly trade help for advanced archery instruction!

Who pays for the traveling expense (I live in Pennsylvania)?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TexARC]

Thanks very much - I have verified that my isp supports MS SQL 2005.

Your message is very concise and should have worked, but unfortunately it seems that sql 2005 express does not offer Import as an option under tasks. I can shrink, restore, detach, backup, and generate scripts as choices under "Tasks", but no import.

The access mdb file is a simple flat file at this point, some 750k in size with 1600 records. The normalized version is
2.24 and has tables for each of the fields in the flat file that are consistently repetitive, ranging from 2 records to around 20 in the table, and each table has an ID key.

Is my only/best option at this point to go buy a copy of the full SQL Server 2005? (yikes)

This will not be an evolving database - my design calls for the ability to log in, add new records, perhaps 100 per year, and to be able to allow general public website display of a given catgory when pulldowns are selected. Choose Men, then Bow, then Age Division, then Distance, and it shows all present and past archers with the year and their scores. VERY simple stuff, once I get past this hurdle. IF I get past this hurdle and don't put an arrow to my head

BTW - if you are near Lancaster, you have the best resource in the entire US for target archery (and hunting, for that matter).

High Performance Coach, Level III, USA Archery
webmaster, Texas State Archery Association
(

http://www.texasarchery.org)

 

[gmmastros]

I suggest you buy the developer edition. The price tag is very reasonable. You cannot use the developer edition for production/deployment stuff, but you can use it to, well... develop the app.

http://www.microsoft.com/products/i...f544888c-2638-48ed-9f0f-d814e8b93ca0&type=ovr

>> BTW - if you are near Lancaster, you have the best resource in the entire US for target archery (and hunting, for that matter).

I was born in Lancaster. Currently live about an hour from the city.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Gooser]

Jeez, George, I had NO IDEA it was that cheap. Maybe I can talk the little woman into letting me buy a copy for the house. Then I can really organize my CD collection.

Another valuable post from the g'mast-er!

Thanks,

v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

 

[TexARC]

Lancaster Archery has one of the few High Performance coaches in the north east, and they literally have the largest showroom in the US. With a resource like that in your back yard, you might find it a fun thing to try. They have an indoor range and I suspect also have bows to get you a taste of the sport.

Archery is a pretty good sport for mental strength aspects, as opposed to the team sports, and a heckuva lot safer

No store in Austin has SQL Server (Frys/Altex/BestBuy, and how weird is that?) so I'll have to order a copy online. I didn't realize I needed it or else I would have gotten it a week ago. Getting jammed up on the time element is a pita.

thank you very much for responding!


High Performance Coach, Level III, USA Archery
webmaster, Texas State Archery Association
(

http://www.texasarchery.org)