Importing MsAccess data To SqlServer

Hi
I am trying to importing the Access Tables and Queryes to Sql Server But I cannot eable to Import the Queryes. What could be the reason.
Should I make the queryes as some seperate format?
Please Help me.Sorry, they don't go. The syntax is different. When you upsize the database from access, the tables will go fine. As for the queries, here is something you can do. Open both access and SQL. Using the SQL pane copy each and every access query in SQL, then paste them into SQL. Then run the query and fix the small amount syntax difference in SQL and Access.

Hope this helps.

Terry

Importing MSAccess data into SQL Tables

I am attempting to import data from an MS Access 2000 table into a SQL
Server 2000 table, but NULL values in the Access table are causing the DTS
import to fail.
I thought that if I defined a column in SQL as 'NOT NULL', but assigned a
default value of '', then it would allow an import containing a NULL, which
it would convert to a ''. But I guess I was wrong? Is there is a way to
import MSAccess data containing nulls into a SQL table where the nulls would
be automatically be converted to ''?
Thanks!
Mark
Here is the definition for the SQL table:
CREATE TABLE [dbo].[creditcard] (
[CARD_NAME] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CARD_ABB] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACTIVE] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[creditcard] ADD
CONSTRAINT [DF_creditcard_CARD_NAME] DEFAULT ('') FOR [CARD_NAME],
CONSTRAINT [DF_creditcard_CARD_ABB] DEFAULT ('') FOR [CARD_ABB],
CONSTRAINT [DF_creditcard_ACTIVE] DEFAULT ('') FOR [ACTIVE]
GO
Mark,
make sure that on the options tab of the transform data task, 'check
constraints' is enabled.
HTH,
Paul Ibison
|||I'm sorry but I don't see the option 'check constraints'. I am running the
'Data Transformation Services Import/Export wizard'.
Here are the screens I see:
1) Choose a data source: I choose the access database
2) Choose a destination: I choose the target database
3) Specify Table Copy or Query: I choose 'Copy table(s) and view(s) from the
source database
4) Select Source Tables and Views: I select my source table and target table
here
5) Save, schedule and replicate package: I choose 'Run Immediately'
6) Completing the DTS Import/Export Wizard: I choose 'Finish' to run the
import.
At this point it appears to import a number of records and then I get the
popup indicating that it can not import the record with the NULL value in
the column.
In all of that I'm afraid I could not see a transform data task. I hate to
be dense, but could you elaborate on where I should look for that?
Many thanks!
Mark
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eNw%23ZyPQEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Mark,
> make sure that on the options tab of the transform data task, 'check
> constraints' is enabled.
> HTH,
> Paul Ibison
>
|||DTS can only carry over constraint if you go between SQL Servers. I think that Access comes with some upsizing
wizard, you might want to check in an Access groups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark Findlay" <mfindlay@.speakeasy.org> wrote in message news:%238kPFkQQEHA.2876@.TK2MSFTNGP09.phx.gbl...
> I'm sorry but I don't see the option 'check constraints'. I am running the
> 'Data Transformation Services Import/Export wizard'.
> Here are the screens I see:
> 1) Choose a data source: I choose the access database
> 2) Choose a destination: I choose the target database
> 3) Specify Table Copy or Query: I choose 'Copy table(s) and view(s) from the
> source database
> 4) Select Source Tables and Views: I select my source table and target table
> here
> 5) Save, schedule and replicate package: I choose 'Run Immediately'
> 6) Completing the DTS Import/Export Wizard: I choose 'Finish' to run the
> import.
> At this point it appears to import a number of records and then I get the
> popup indicating that it can not import the record with the NULL value in
> the column.
> In all of that I'm afraid I could not see a transform data task. I hate to
> be dense, but could you elaborate on where I should look for that?
> Many thanks!
> Mark
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eNw%23ZyPQEHA.3708@.TK2MSFTNGP10.phx.gbl...
>
|||Mark,
the package uses a data transform task in the background which has loads of
properties that are not exposed in the wizard, so the best thing to do is to
save the package without running it. After that, open it and double-click
the data transform task. On the final tab, you'll see the option I'm talking
about. The check constraints option should initiate the default's use.
HTH,
Paul Ibison

Importing data from MSAccess to SQL2000

Hi,
I am very, very new to SQL, so be patient.
On a W2K3-server runs SQL2000 (Sp3a). I have a database in MSAccess (2003) and want to import
the data with the wizard. All went well and I got the whole database in SQL.
Now I am back in VS.net and want to create a SQLAdapter and when doing that I got the warning:
UPDATE and DELETE statement. Could not determin which columns uniquely identify the rows for
"<Table>".
The structure of "<Table>" in MSAccess was:
Id (auto) as primary key
Field1 (text,50)
Field2 (text,50)
That's all.
The structure of the SQL-table after importing-Wizard:
Id (int, Not Null)
Field1 (nvarchar(50),Null)
Field2 (nvarchar(50),Null)
Why cannot SQL recognise this primary key ?
Regards, Ger.Hey,
Is the ID field set as the primary key (double-click table), when showing columns it should have the yellow key icon.|||Thx BMains for answering so quick.
I doubleclicked in Enterprise Manager (I presumed you mean that) on the table and found this
Key ID Name DataType Size Nulls Default
Id int 4 not checked
test nvarchar 50 checked
test1 nvarchar 50 checked
I think, this is what you mean. My conclusion is: Id is not a primary key in this SQL-table.
Q1: why not, because when importing from MSAccess there was a primary key defined in the Access-table.
Q2: how can I set a primary key to the above file ?
Thanks in advance,
regards, Ger.|||

GerEielts wrote:

Q2: how can I set a primary key to the above file ?.

Found this setting, no need for an answer. It can be set by Enterprise Manager,
<database>, Tables, <wanted table>, rightclick and Design Table. In here set the
primary key to a field.
Hope this helps for others too.
regards, Ger