Upsize Access 2000db (1 Viewer)

I have split my database to front end and back end. The back end holds only
the tables. I used the upsizing wizard in access 2000. The tables were skipped or export failed. What is going on? Help! I should mention here that I have installed SQL Server 2005 Express on this PC. I would like to convert the back end with the tables into SQL Server with linked tables to my Access Front End. I can attach a zip of the "PED40_be.mdb" if it helps explain the upsizing screens. (Only 33.7KB) I have removed most all of data to get the size down.
Upsizing screen 1 -Upsizing Wizard- Checked "Create New Database";then checked "Next"
Upsizing screen 2 -
Question:What SQL Server would you like to use for this database?I entered "(local)\SQLExpress"
Question:Login Id- I don't know what to put in here?
Questionassword- I don't know what to put in here?
I have not set up a SQL database yet so I don't what to enter here?
Question:What do youwant to name your new SQL Server Database?
I entered "PED40_be"
Then I click next to go to the next screen
Upsizing screen 3-
Question: Which tables do you want to export to SQL Server?
I selected all the tables once. I received skipped table report for all tables. Then I tried the wizard again and selected only 1 table. Again the report stated that this 1 table was skipped.
Upsizing screen 3- "The upsizing wizard can export table attributes in addition to data." I left the items checked that were checked on the screen and clicked NEXT to go to the next Screen.
Upsizing screen4-
Question: The upsizing wizard can modify the existing application or create a new application to work with the SQL Server Database. Which application changes do you want to make? I selected-Link SQL Server Tables to existing Application. Then I click next.
Upsizing screen 5- The upsizing wizard has all the information it needs to upsize your database. Then I click FINISH.

Then I get the report that "Table was skipped or export failed"
I am experienced in Access but know little about SQL Server. Anything could be wrong.
I have attached a copy of the Upsizing Report.
Any Help greatly appreciated.
Bill D

Cannot help but want to find out about this myself -

BillD said:

Question:Login Id- I don't know what to put in here?

Click to expand...

If you are using sql authentication you would put in your username from SQL Server. But, most of the time you will probably want Windows Authentication so just check the Use Trusted Connection checkbox.

Questionassword- I don't know what to put in here?

Click to expand...

same as the username question

I have not set up a SQL database yet so I don't what to enter here?
Question:What do youwant to name your new SQL Server Database?
I entered "PED40_be"

Click to expand...

That is fine but make sure you don't use quotes around it, if you did.

Upsizing screen 3-
Question: Which tables do you want to export to SQL Server?
I selected all the tables once. I received skipped table report for all tables. Then I tried the wizard again and selected only 1 table. Again the report stated that this 1 table was skipped.

Click to expand...

Make sure you look at all of the prep work steps for your tables and queries in the reference I gave before (although I can't seem to find the same article as before, here's another: http://sqlserver2000.databases.aspfaq.com/how-do-i-upsize-from-access-to-sql-server.html)

Upsizing screen4-
Question: The upsizing wizard can modify the existing application or create a new application to work with the SQL Server Database. Which application changes do you want to make? I selected-Link SQL Server Tables to existing Application. Then I click next.

Click to expand...

should be fine to do

Upsizing screen 5- The upsizing wizard has all the information it needs to upsize your database. Then I click FINISH.

Then I get the report that "Table was skipped or export failed"
I am experienced in Access but know little about SQL Server. Anything could be wrong.

Click to expand...

I'm assuming that this is due to not prepping your tables properly before upsizing. Check the references I gave and also do a Google search on preparing for upsizing to SQL Server to get more info about that.

Oh, and Welcome to Access World Forums

I will check out the prepping of tables and post my results. I do not think that this is the problem as I tried to upsize with only 1 of the tables and I received the same result. Table skipped or export failed. The table I selected only had 3 text fields in it. (Yes,No and maybe)
Thanks again,BillD

Bill - if I remember right your tables MUST have a primary key (unique - no duplicates) defined for it to work right. Your description of that table doesn't seem to have it but you would have to let me know if it actually did have a primary key field or not.

The simple table did not have a primary key. I used this table for a combo drop-down to select whether someone voted or not. Fields -Yes, No, and Maybe
I will try a simple table with a primary key.

Thanks again, BillD

Hey Bill I did find this:

The Upsizing Wizard doesn't like tables that don't have at least one index or unique constraint. The Upsizing Wizard will upsize an indexless table, but it will be read-only in SQL Server. Fortunately, the solution to this requirement is simple: Add an index to each table that doesn't already have one. Once you've upsized the database, remember to remove the index.

Click to expand...

There is more good stuff regarding the upsize in the same article here:
http://articles.techrepublic.com.com/5100-22_11-5035130.html?tag=rbxccnbtr1

Bob:
I tried to upsize the back end Access 2000 database to SQL Server 2005 Express again for the 100th time. This time I tried to upsize only 1 table called "Status". The only field in the table is "Status" which is the primary key.The allow indexing is set to Yes-No Duplicates. Allow Zero Length is set to "No". Even this 1 table will not upsize to an SQL Server Database. When asked -What table attributes do you want? I checked only indexes on 1 attempt. When asked-What data options do you want to include? I tried only table structure. I tried to link the tables on 1 try. I tried to create a new Access client/server application on another try.
The wizard 2nd screen- does not have the "Use Trusted Connection" check box available. I have not entered a Login ID or Password.
WHAT ELSE COULD I TRY?
Exhausted and fustrated.

BillD

Bill, sorry that it isn't working for you. I think we're down to maybe using DTS to move the tables to SQL and then you can manually link to in the frontend. I don't see any other option. I hope you are familiar with using SQL Server DTS.

Hi there,

Sorry to jump in, but do you get any kind of detailed error message in the upsize log?

quote:

"Use Trusted Connection" check box available. I have not entered a Login ID or Password

Try creating a sql server login and password and use that?

I personally think the upsizing wizard sucks, it creates timestamps when you dont need them, it creates all text colums as n'varchar or n'text, when varchar is what you want anyway (n'varchar and n'text are twice the size!)
Unless there are lots of tables I would just create new tables in SQL server and then import the data using DTS. At the end of the day you will need to find out how to do this anyway, so why not start at the beginning.

Could you tell me how to create an sql server login and password. I don't know what DTS is but in time I can do anything. Thanks for the advice.
BillD

Hi there

You need the management tools for SQL server express, which can be found here

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

*shameless typo edit*

Thanks for the site location. I will try this.

Fantastic!!!!!

SQL_Hell said:

Hi there

You need to management tools for SQL server express, which can be found here

http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

Click to expand...

THANK YOU, THANK YOU, THANK YOU!!! I wasn't aware that a management tool existed for SQL Server Express 2005 and have really wanted one. This is awesome! Thanks for providing that.

I looked at the management tools for SQL server express at the site provided.
A prerequisite seems to be windows XP Professional. I ahve windows XP Media Center Edition. I cannot find info if this is OK. ShoulsdI try it anyway?

By the way, I created a new database from scratch win Access 2000. Only one table in database. No forms, queries or reports. The table has an ID field as the primary key and 3 other fields; LastName, FirstName and MiddleName.

I tried the upsizing wizard; I get the same response. ERROR-"Table was skipped, or Export failed". I am stumped because I don't know SQL. I can't see a database anywhere that has been created. Would the upsizing not cause a database to be created in some form even if there were errors.

Help! Help! I am sinking and starting to give up - Please pull me out.
Thanks for all your help. Don't you get discouraged.
BillD - This can't really be that difficult.

Hi there,

Yes try it anyway.

Is SQL server express actually running properly on your machine? check this by:

right click on My computer, select manage
select services
and see if the SQL server service is running

I downloaded the SQL Server Management Studio Express. It seemed to install OK.

Here is what I found under MY Computer-Services and Applications-SQL Server Configuratioin Manager-SQL Server 2005 Services
SQL Server (SQLEXPRESS) - Running - Automatic
SQL Server Browser - Running - Automatic

I have not been able to set a table as yet. I have given up on the upsizing wizard.
Local Instance is 'bill\sqlexpress'
I have also downloaded SSMA. Haven't looked at it yet.

Thanks again.
BillD