Exporting tables from SQL Server into MS Access

Question

I'm trying to export tables from SQL Server into MS Access using the data import/export feature. Everything works well but for 2 things:

1. Primary key constraint is not being exported to MS Access and even the identity property. Ideally I wanted the country_id column to be an AutoNumber / primary key column in MS access.

2. bit column is being converted to Integer in MS access. I wanted it to be a Yes/No column.

Can somebody help me with this?

This here is my SQL Server code:

CREATE TABLE country
(
    id_country int IDENTITY PRIMARY KEY not null,
    my_tinyint tinyint,
    my_single real,
    my_double float,
    my_bit bit,
    my_char char(7),
    my_longchar text
);

Answer 1

You cannot create an AutoNumber field directly with DDL. The best you can do is create a Primary Key field. The following DDL query works against my 2013 Access db:

CREATE TABLE country
 (
   id_country LONG CONSTRAINT PK_id_country PRIMARY KEY,
   my_tinyint integer,
   my_single single,
   my_double double,
   my_bit integer,
   my_char text(7),
   my_longchar memo
 )

This would not create an incrementing field, however. You'd have to use DAO or ADOX to handle that, or do it manually in the Access interface.Here's a SO question that shows how to do that: How to create table with Autonumber field in MS - Access at run time?