Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Migrating from MsSQL to MsAcess

Discussion in 'Information Technology' started by hack_tick, Nov 5, 2005.

  1. hack_tick

    hack_tick
    Expand Collapse
    Guest

    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?
     
  2. Loading...

    Similar Threads Forum Date
    Sikh Foundation Migrating Identities by Simran Kaur Sikh Organisations Aug 6, 2011

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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

    --
    Brendan Reynolds


    "hack_tick" <sharma.vasudev@gmail.com> wrote in message
    news:1131099698.512059.30210@o13g2000cwo.googlegroups.com...
    > 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?
    >
     
  4. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    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 (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  5. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest


    > 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....
     
  6. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    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

    --
    Brendan Reynolds

    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    news:OgvkbCT4FHA.3948@TK2MSFTNGP09.phx.gbl...
    >
    >> 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....
    >
    >
    >
     

Share This Page