Tags: access, andfields, code, codefunction, create, database, following, front-end, microsoft, mysql, oracle, run, sql, table, tables, vba

CREATE TABLE in SQL from VBA

On Database » Microsoft Access

6,061 words with 6 Comments; publish: Sun, 01 Jun 2008 11:19:00 GMT; (25066.41, « »)

Im trying to run code from my Access front-end that can create tables and

fields in a SQL database. I run the following code:

Function ExecuteSQLDDL(SQLString As String, DBName As String)

Dim MyDB As dao.Database

Dim MyQ As dao.QueryDef

Set MyDB = DBEngine.Workspaces(0).OpenDatabase(DBName, 1, True,

"ODBC;DSN=" & DBName)

Set MyQ = MyDB.CreateQueryDef("")

MyQ.SQL = SQLString

MsgBox MyQ.SQL

MyQ.Execute

MyQ.Close

End Function

I use the following SQL string:

CREATE TABLE Table1 (Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, MyText

TEXT (10))

An error occurs at the line MyQ.Execute. "Cannot perform this operation"

Anybody have a clue what the problem is?

All Comments

Leave a comment...

  • 6 Comments
    • "Morten" <Morten.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      news:E504837A-FF98-43D6-AED6-F5291983030D.ms-access.todaysummary.com.microsoft.com...

      > Im trying to run code from my Access front-end that can create tables and

      > fields in a SQL database. I run the following code:

      > Function ExecuteSQLDDL(SQLString As String, DBName As String)

      > Dim MyDB As dao.Database

      > Dim MyQ As dao.QueryDef

      > Set MyDB = DBEngine.Workspaces(0).OpenDatabase(DBName, 1, True,

      > "ODBC;DSN=" & DBName)

      > Set MyQ = MyDB.CreateQueryDef("")

      > MyQ.SQL = SQLString

      > MsgBox MyQ.SQL

      > MyQ.Execute

      > MyQ.Close

      > End Function

      > I use the following SQL string:

      > CREATE TABLE Table1 (Id COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, MyText

      > TEXT (10))

      > An error occurs at the line MyQ.Execute. "Cannot perform this operation"

      > Anybody have a clue what the problem is?

      When you say "a SQL database" I presume that you mean a SQL Server database.

      If that's so, then you have to make this a pass-through query, which means

      that you need to set the query's Connect property to be an appropriate ODBC

      connection string, and it's ReturnsRecords property to false.

      #1; Sun, 01 Jun 2008 11:20:00 GMT
    • Brian,

      Thanks for your suggestion. Yes, it is an SQL server database. Is this

      what you mean:

      Function ExecuteSQLDDL(SQLString As String, DBName As String)

      Dim MyDB As DAO.Database

      Dim MyQ As DAO.QueryDef

      Set MyDB = DBEngine.Workspaces(0).OpenDatabase(DBName, 1, False,

      "ODBC;DSN=" & DBName)

      Set MyQ = MyDB.CreateQueryDef("")

      MyQ.SQL = SQLString

      MyQ.Execute dbSQLPassThrough

      MyQ.Close

      End Function

      #2; Sun, 01 Jun 2008 11:21:00 GMT
    • I tried this, but I got the same result, i.e. "Cannot perform this operation"

      Function ExecuteSQLDDL(SQLString As String, DBName As String)

      Dim MyDB As DAO.Database

      Dim MyQ As DAO.QueryDef

      Set MyDB = DBEngine.Workspaces(0).OpenDatabase(DBName, 1, True,

      "ODBC;DSN=" & DBName)

      Set MyQ = MyDB.CreateQueryDef("")

      MyQ.SQL = SQLString

      MyQ.ReturnsRecords = False

      MyQ.Execute dbSQLPassThrough

      MyQ.Close

      End Function

      #3; Sun, 01 Jun 2008 11:22:00 GMT
    • "Morten" <Morten.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      news:6FB6358F-0C60-4E3B-BEA2-25644CBA47BF.ms-access.todaysummary.com.microsoft.com...

      > I tried this, but I got the same result, i.e. "Cannot perform this

      operation"

      > Function ExecuteSQLDDL(SQLString As String, DBName As String)

      > Dim MyDB As DAO.Database

      > Dim MyQ As DAO.QueryDef

      > Set MyDB = DBEngine.Workspaces(0).OpenDatabase(DBName, 1, True,

      > "ODBC;DSN=" & DBName)

      > Set MyQ = MyDB.CreateQueryDef("")

      > MyQ.SQL = SQLString

      > MyQ.ReturnsRecords = False

      > MyQ.Execute dbSQLPassThrough

      > MyQ.Close

      > End Function

      >

      You need to set the database object to the current database, and set the

      Connect property of the QUERY to your ODBC connection string. I usually do

      this by copying the connect property from one of the tables, assuming that

      you've got linked tables in the database i.e.

      Set MyDB = CurrentDb

      Set MyQ = MyDB.CreateQueryDef("")

      MyQ.Connect = MyDB.TableDefs("SomeLinkedTable").Connect

      MyQ.SQL = SQLString

      MyQ.ReturnsRecords = False

      MyQ.Execute

      #4; Sun, 01 Jun 2008 11:23:00 GMT
    • or, create an ODBCdirect workspace:

      [vbcol=seagreen]

      set MyWs = application.DBEngine.CreateWorkspace("","user","pw d",dbuseodbc)

      set mydb = myws.opendatabase("",dbDriverPrompt,false,sconnect )

      mydb.Execute sqlstring

      (david)

      "Brian" <bcap.ms-access.todaysummary.com.IHATESPAMclara.co.uk> wrote in message

      news:1102854574.26185.0.ms-access.todaysummary.com.spandrell.news.uk.clara.ne t...

      > "Morten" <Morten.ms-access.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:6FB6358F-0C60-4E3B-BEA2-25644CBA47BF.ms-access.todaysummary.com.microsoft.com...

      > operation"

      > You need to set the database object to the current database, and set the

      > Connect property of the QUERY to your ODBC connection string. I usually

      do

      > this by copying the connect property from one of the tables, assuming that

      > you've got linked tables in the database i.e.

      > Set MyDB = CurrentDb

      > Set MyQ = MyDB.CreateQueryDef("")

      > MyQ.Connect = MyDB.TableDefs("SomeLinkedTable").Connect

      > MyQ.SQL = SQLString

      > MyQ.ReturnsRecords = False

      > MyQ.Execute

      >

      #5; Sun, 01 Jun 2008 11:24:00 GMT
    • Thanks for all your help. I got it working using the following code:

      Function ExecuteSQLDDL(SQLstring As String, DSNName As String)

      Dim MySQLDB As DAO.Database

      Dim MyQ As DAO.QueryDef

      On Error GoTo Trap

      Set MySQLDB = CurrentDb

      Set MyQ = MySQLDB.CreateQueryDef("")

      MyQ.Connect = "ODBC;DSN=" & DSNName

      Debug.Print MyQ.Connect

      MyQ.SQL = SQLstring

      Debug.Print MyQ.SQL

      MyQ.ReturnsRecords = False

      MyQ.Execute

      MyQ.Close

      MySQLDB.Close

      GoTo EndFunc

      Trap:

      ' DAO Error Handler

      Dim MyError As Error

      MsgBox Errors.Count

      For Each MyError In DBEngine.Errors

      With MyError

      MsgBox .Number & " " & .Description

      End With

      Next MyError

      MyQ.Close

      MySQLDB.Close

      EndFunc:

      End Function

      RGDS

      Morten

      #6; Sun, 01 Jun 2008 11:25:00 GMT