Migrating from MsSQL to MsAcess

[hack_tick]

hi there guys!
I am looking for MSAcess equivalent of following MsSQL SQL Query

create table Erfahrung (
id_num int identity(1,1) not null,
vorgehen_number int null,
application_type varchar(20) null,
erfahrungsdaten varchar(255) null,
primary key(id_num)
);

any suggestions?

 

[Brendan Reynolds]

While your SQL would not work with the Access query designer, it will work
just fine with JET 4 (Access 2000 and later) if executed via ADO ...

Public Sub TestSQL()

Dim strSQL As String

strSQL = "create table Erfahrung (" & _
"id_num int identity(1,1) not null, " & _
"vorgehen_number int null, " & _
"application_type varchar(20) null, " & _
"erfahrungsdaten varchar(255) null, " & _
"Primary Key(id_num));"

CurrentProject.Connection.Execute strSQL, , adCmdText

End Sub

 

[Albert D.Kallal]

I am quite weak in using JET ddl

However, I just cut and pasted your statement into the query builder..and it
ran just fine.

I don't know what version of ms-access, but for a2002, and a2003, you can go
in tools

tools->options->table/queries tab.

Just make sure you "check" the "sql Server Compatible syntax (ANSI 92)

If you do the above, then you can use your sql as you have it....

If you don't want to enable this feature, then you can also execute the ddl
as a ado execute, (as opposed to dao), and it will also work...

from the debug window, the following will not work

currentdb.execute "your sql goes here"

However, if you use the ado object, then it will

currentproject.Connection.Execute "your sql goes here"

So, if you don't "turn on" the ANSI compatibility, then you can use hte
current project.connection.

However, if you do turn on the ANSI compatibility, then you can just paste
your sql into the query builder..and it works fine....

 

[Albert D.Kallal]

While your SQL would not work with the Access query designer, it will work
just fine with JET 4 (Access 2000 and later) if executed via ADO ...

And, as I very recnelty learned...if you enable the sql compablbity (ansi
92)..then thsoe ddl statemtnes also work direclity when pasted into the
query bilder.

(and, even with line breaks etc...the poserts sql works just fine....).

So, yes...either use your suggestion...or turn on sql ansi compaiblity..and
the query builder will also work....

 

[Brendan Reynolds]

Thanks Albert. I've so far avoided ANSI 92 mode because of the bug described
in the following KB article ...

http://support.microsoft.com/default.aspx?scid=kb;en-us;824189