ALTER (or CREATE) TABLE/Ole Object Data Type in MS Access

[JohnJohn]

Hello. I need to be able to create a column in an Access table that has the
"Ole Object" data type, but I need to be able to do it using SQL syntax
(ALTER TABLE or CREATE TABLE). For the life of me, I can't find any
information on how or if this can be done, which means to me that it probably
can't be done this way. My database is an Access 2003 database, and the SQL
Syntax to create this column is called from a Visual Basic 6 application
using the ADO 2.8 library.

Any ideas? Need any more info?

Thanks,
John

[MikeD]

Post by JohnJohn
Hello. I need to be able to create a column in an Access table that has the
"Ole Object" data type, but I need to be able to do it using SQL syntax
(ALTER TABLE or CREATE TABLE). For the life of me, I can't find any
information on how or if this can be done, which means to me that it probably
can't be done this way. My database is an Access 2003 database, and the SQL
Syntax to create this column is called from a Visual Basic 6 application
using the ADO 2.8 library.
Any ideas? Need any more info?

For an Access database, it'd probably be better to use ADOX than SQL data
definition statements. Check Help (it's part of ADO's Help) on that and I'll
bet you'll find exactly what you need. ADOX is an "extension" to ADO
intended for creating/modifying schema and security. It's fully supported by
the Jet OLEDB Provider but only partially supported by other providers. So,
as long as you have no plans to ever update to a more robust DBMS, you'll be
fine using it. The library you want add a reference to is "Microsoft ADO
Ext. x.x for DDL and Security".

[JohnJohn]

Thanks Mike! I'll give it a shot.

John

Post by MikeD

Post by JohnJohn
Hello. I need to be able to create a column in an Access table that has the
"Ole Object" data type, but I need to be able to do it using SQL syntax
(ALTER TABLE or CREATE TABLE). For the life of me, I can't find any
information on how or if this can be done, which means to me that it probably
can't be done this way. My database is an Access 2003 database, and the SQL
Syntax to create this column is called from a Visual Basic 6 application
using the ADO 2.8 library.
Any ideas? Need any more info?

For an Access database, it'd probably be better to use ADOX than SQL data
definition statements. Check Help (it's part of ADO's Help) on that and I'll
bet you'll find exactly what you need. ADOX is an "extension" to ADO
intended for creating/modifying schema and security. It's fully supported by
the Jet OLEDB Provider but only partially supported by other providers. So,
as long as you have no plans to ever update to a more robust DBMS, you'll be
fine using it. The library you want add a reference to is "Microsoft ADO
Ext. x.x for DDL and Security".
--
Mike
Microsoft MVP Visual Basic

[Steve Barnett]

I've just found this code in an old VB6 program... not sure if it helps (it
was DAO) but the syntax should be similar to what you want. The caSheet
field is the one you're interested in... Give it a try.

sSql = "CREATE TABLE Calculations (" & _
"[caKey] Text(12) NOT NULL, " & _
"[caCreated] Date NOT NULL, " & _
"[caValid] Bit NOT NULL, " & _
"[caSheet] LongBinary , " & _
"CONSTRAINT ixKey PRIMARY KEY ([caKey]) " & _
");"
dbTemp.Execute sSql

Steve

Post by JohnJohn
Thanks Mike! I'll give it a shot.
John

Post by MikeD

Post by JohnJohn
Hello. I need to be able to create a column in an Access table that
has
the
"Ole Object" data type, but I need to be able to do it using SQL syntax
(ALTER TABLE or CREATE TABLE). For the life of me, I can't find any
information on how or if this can be done, which means to me that it probably
can't be done this way. My database is an Access 2003 database, and
the
SQL
Syntax to create this column is called from a Visual Basic 6 application
using the ADO 2.8 library.
Any ideas? Need any more info?

For an Access database, it'd probably be better to use ADOX than SQL data
definition statements. Check Help (it's part of ADO's Help) on that and I'll
bet you'll find exactly what you need. ADOX is an "extension" to ADO
intended for creating/modifying schema and security. It's fully supported by
the Jet OLEDB Provider but only partially supported by other providers.
So,
as long as you have no plans to ever update to a more robust DBMS, you'll be
fine using it. The library you want add a reference to is "Microsoft ADO
Ext. x.x for DDL and Security".
--
Mike
Microsoft MVP Visual Basic

[Paul Clement]

On Wed, 2 Aug 2006 06:11:02 -0700, JohnJohn <***@discussions.microsoft.com> wrote:

BTW, I responded to this post in the vb.database forum.

While you can use ADOX or DAO I wouldn't recommend either. The problem is that if your object model
changes (as has frequently occurred with data access technologies) you have to completely rewrite
the code.

Jet SQL DDL should be fine.The Jet DDL synonyms for this data type are:

IMAGE
LONGBINARY
GENERAL
OLEOBJECT

ALTER TABLE tblCustomers
ADD COLUMN MyOleObjectColumn OLEOBJECT

For more info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp


Paul
~~~~
Microsoft MVP (Visual Basic)