HELP! How to Upsize from Access 2002 to SQL Express

Problem:
Upsize a backend MSAccess 2002 Database to SQL Express 2005
Explored:
Tried using the upsizing wizard from Office XP(2002), Two tables always get skipped.
***! The two tables skipped data only, the tablename and data structure were created.
Tried to install UPSize Pro, installation failed.
I decide to try it in VWD 2005, here is my code so far but it keeps erroring out.

Dim cnAs System.Data.OleDb.OleDbConnection
Dim cmdAs System.Data.OleDb.OleDbDataAdapter
Dim dsAsNew System.Data.DataSet()
cn =New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\Documents and Settings\Bill\My Documents\Visual Studio 2005\WebSites\WebSite1\App_Data\Data for Database.mdb';Persist Security Info=True")
cmd =New System.Data.OleDb.OleDbDataAdapter("select * from Service_Orders", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()

Dim connDestAsNew Data.SqlClient.SqlConnection("Data Source=WLOCKLAPTOP\SQLEXPRESS;Initial Catalog='Data for DatabaseSQLND1';Integrated Security=True")
connDest.Open()
Dim oBCPAsNew Data.SqlClient.SqlBulkCopy(connDest)
oBCP.DestinationTableName ="Service_Orders"
oBCP.WriteToServer(ds)
oBCP.Close()
connDest.Close()

It erors on Line
oBCP.WriteToServer(ds)
with.....
System.InvalidCastException was unhandled by user code
Message="Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.IDataReader'."
Source="App_Web_hb6xyamq"
StackTrace:
at ASP.xfer_data_aspx.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\Bill\My Documents\Visual Studio 2005\WebSites\WebSite1\xfer data.aspx:line 30
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

I'm open for ANY suggestions, I do not have access to DTS, its not in the Express addition.
Thanks in advance.
Bill

You can use access. You can use the export feature in access togo straight to SQL Server and the data types will be auto converted.
|||I really appreciazte the help, using the export was a learning exprience, and also it showed the exact errors I was having. It also failed, but it gave me the reason for the failure instead of the generic 'table was skipped' error from the upsizing wizard report.
I ran this query in MS Access to 'prep' the tables before upsizing.
SELECT * FROM TableName
WHERE (((Date_Entered)<#1/1/1753# Or (Date_Entered)>#12/31/9999#));
That showed me the bad dates, there were only about four records returned from 8000. I fixed the dates, some were 2/10/085, etc, and I changed them to the real date 2/10/2005, how they got like that is beyond me.
I also had to change a field that was using currency to general number.
I ran the upsize wizard and all tables upsized.
In SQL Server Management Studio Express (Free Download and very helpful GUI from Microsoft) I then changed the old currency field to a money field without a hitch.
It seems that SQL is stricter on dates than MS Access.
Life is good!

HELP! " the table schema changed after the cursor was declared"

I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

LiliOriginally posted by lili3000
I have a DTS package working from backend and command line. Then I did an enhancement. It works from backend but not from command line. The error says:

Step Error Description:Could not complete cursor operation because the table schema changed after the cursor was declared.

But I don't see "table schema" change?!! Any idea?

Thanks,

Lili

I ran in to the same problem. As it turned out I was running the application at 1:00 am to process credit card transaction (This application is a batched response from shipper) Anyway. I had inadvertly schedule maintence during this time. The problem was resolved by moving the Maintence time.

I hope this helps.

Terry

Help with weird error

I am working with Access frontends and a SQL Server 2000 backend.
One user gets the following error when trying to insert directly into
a table through access or through a form into that same table: "String
or binary data would be truncated". It doesn't matter into which type
of column the data is entered. Int, datetime, varchar all produce the
same error.
This user has access to the db this table is stored in and can write
to other tables in that db with no problem. In fact, none of the other
users with the same permission settings to this db has this problem.
They can insert into or update this same table just fine.
This is also pc independent for this user. It does seem to be a matter
of account setting though, because none of the other users has this
problem.
What could be wrong? Does anybody have any ideas? As far as I know
this user has the exact same permission setting as others. What else
could I look into to solve this mystery?
Thanks for your help.> One user gets the following error when trying to insert directly into
> a table through access or through a form into that same table: "String
> or binary data would be truncated".
This sounds like a string is attempting to be inserted but it is too long to
fit in the column. This is not a "weird" error, in fact it is quite common.
I highly doubt it has anything to do with this user or his/her permissions,
unless there is a trigger on the table and the failure is occurring because
his domain username is too long to fit into the auditing table.|||I fixed the problem. As it turned out, for this particular user the
username is saved with its domain name. The table field to which this
user info is saved wasn't large enough to hold both the username and
domain name.
Thanks for your reply, Aaron. As you predicted, the username was too
long to be stored.
On Mar 11, 9:33 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> > One user gets the following error when trying to insert directly into
> > a table through access or through a form into that same table: "String
> > or binary data would be truncated".
> This sounds like a string is attempting to be inserted but it is too long to
> fit in the column. This is not a "weird" error, in fact it is quite common.
> I highly doubt it has anything to do with this user or his/her permissions,
> unless there is a trigger on the table and the failure is occurring because
> his domain username is too long to fit into the auditing table.

Help with weird error

I am working with Access frontends and a SQL Server 2000 backend.
One user gets the following error when trying to insert directly into
a table through access or through a form into that same table: "String
or binary data would be truncated". It doesn't matter into which type
of column the data is entered. Int, datetime, varchar all produce the
same error.
This user has access to the db this table is stored in and can write
to other tables in that db with no problem. In fact, none of the other
users with the same permission settings to this db has this problem.
They can insert into or update this same table just fine.
This is also pc independent for this user. It does seem to be a matter
of account setting though, because none of the other users has this
problem.
What could be wrong? Does anybody have any ideas? As far as I know
this user has the exact same permission setting as others. What else
could I look into to solve this mystery?
Thanks for your help.
> One user gets the following error when trying to insert directly into
> a table through access or through a form into that same table: "String
> or binary data would be truncated".
This sounds like a string is attempting to be inserted but it is too long to
fit in the column. This is not a "weird" error, in fact it is quite common.
I highly doubt it has anything to do with this user or his/her permissions,
unless there is a trigger on the table and the failure is occurring because
his domain username is too long to fit into the auditing table.
|||I fixed the problem. As it turned out, for this particular user the
username is saved with its domain name. The table field to which this
user info is saved wasn't large enough to hold both the username and
domain name.
Thanks for your reply, Aaron. As you predicted, the username was too
long to be stored.
On Mar 11, 9:33 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> This sounds like a string is attempting to be inserted but it is too long to
> fit in the column. This is not a "weird" error, in fact it is quite common.
> I highly doubt it has anything to do with this user or his/her permissions,
> unless there is a trigger on the table and the failure is occurring because
> his domain username is too long to fit into the auditing table.