MSSQL + Create tables via VB

[Maartin]

Hi all,

I need some help, I want to create a table in MSSQL 7.0 using VB6 with DAO and ODBC. I used a declaration of connection and when I try to go about creating the TableDef I keep on getting and error 'Object not supported for this type object.
I tried various ways but no luck.

Here is a snipet of the db open and the the create attempt:
'|Db decl
Dim wrkMain As Workspace
Dim dbMain As Connection
Dim rsMain As Recordset

'|Connect
Set wrkMain = CreateWorkspace("Transfers", "", "", bUseODBC)
Set dbMain = wrkMain.OpenConnection("Transfers",dbDriverComplete,False,"ODBC;DATABASE=Emmadb;UID=sa;PWD=;DSN=EmmaDB")

'|create table
Dim tblDef As TableDef
Dim prpLoop As Property
Dim dbTemp As Database

Set dbTemp = dbMain.Database
Set tblDef = dbTemp.CreateTableDef("Transfer_Hist") OR
Set tblDef = dbMain.Database.CreateTableDef("Transfer_Hist")

Both the last lines of code give the same error, anybody got ideas or suggestions ?

Thanks.

Maartin.

[paulw]

I may be wrong on this, but I do not believe that you can use the Jet workspace this way. You need to use a SQL pass through query, such as "CREATE TABLE MyTable...."

TableDefs are Jet representations of underlying SQL Server data structures, I don't think that you can use them directly to create tables like this.

If I am wrong (and I may be), let me know. I am interested.

For sure though, the pass-through stuff will work.

Cheers,

Paul.