UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Tool To Extract Table Data Definition Statements From Access Databases, Any Version    
 
   
isladogs
post Jun 4 2020, 03:13 AM
Post#21


UtterAccess VIP
Posts: 2,402
Joined: 4-June 18
From: Somerset, UK


Thanks - missed the original link.
Agree regarding the 'lack of time' to do development work during the lockdown

For info, I sent you a PM about an unrelated matter at AWF

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Jun 4 2020, 03:22 AM
Post#22


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


I saw it late last night, Colin. Sorry I haven't responded yet.

Again, spare time will be the key. Will get back to you when I've had a poke around, but can't guarantee that will be in the next couple of days.

--------------------


Regards,

David Marten
Go to the top of the page
 
isladogs
post Jun 4 2020, 03:27 AM
Post#23


UtterAccess VIP
Posts: 2,402
Joined: 4-June 18
From: Somerset, UK


BTW - I got an email response at 08:44 to this thread from a user called @gretchenathis but I can't find the post. Has it been removed?

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Jun 4 2020, 03:34 AM
Post#24


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


I blitzed it within a minute of its arrival! Spammer.

--------------------


Regards,

David Marten
Go to the top of the page
 
ADezii
post Jun 4 2020, 11:33 AM
Post#25



Posts: 3,089
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
I'm sure it will barf when having to deal with dbs containing Attachment or Lookup fields!

From my personal experience and related research, you may want to start barfing. It appears as though you cannot create an Attachment Field dynamically using either SQL or VBA. I have tried both approaches to no avail, but if anything positive comes up, I'll let you know. pullhair.gif
Go to the top of the page
 
cheekybuddha
post Jun 4 2020, 12:34 PM
Post#26


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


>> It appears as though you cannot create an Attachment Field dynamically using either SQL or VBA <<

Hmmm..., really?

I just created a table with PK and a text field.
tblFile
ID: PK, auto
FileName: text

Then I made this function (based on Allen Browne):
CODE
Function AddTblField(tblName As String, fldName As String, fldType As Long, Optional fldSize As Variant) As Boolean

  Dim db As DAO.Database
  
  Set db = CurrentDb
  With db.TableDefs(tblName)
    .Fields.Append .CreateField(fldName, fldType, fldSize)
  End With
  AddTblField = Err = 0
  Set db = Nothing
  
End Function


Immediate Window:
CODE
?AddTblField("tblFile", "FileAtt", dbAttachment)
True


Haven't tried DDL.
Attached File(s)
Attached File  20200604_DAO_attachment_fld.png ( 7.5K )Number of downloads: 0
 

--------------------


Regards,

David Marten
Go to the top of the page
 
ADezii
post Jun 4 2020, 12:47 PM
Post#27



Posts: 3,089
Joined: 4-February 07
From: USA, Florida, Delray Beach


Very strange! The following Code will not work with my Version of Access (Microsoft 365), but will with other Versions. dbAttachment is not even a member of the DataTypeEnum Enumeration and will generate a Runtime Error as listed below:
CODE
Dim MyDB As dao.Database
Dim tdfNew As dao.TableDef
Dim fldNew As dao.Field

CurrentDb.TableDefs.Delete "Demo"

Set MyDB = CurrentDb

Set tdfNew = MyDB.CreateTableDef("Demo")
  
With tdfNew
  Set fldNew = .CreateField("ID", dbLong)           'Works
               .Fields.Append fldNew
               .Fields.Refresh
  Set fldNew = .CreateField("FName", dbText, 50)    'Works
               .Fields.Append fldNew
               .Fields.Refresh
  Set fldNew = .CreateField("LName", dbText, 50)    'Works
               .Fields.Append fldNew
               .Fields.Refresh
  Set fldNew = .CreateField("MI", dbText, 1)        'Works
               .Fields.Append fldNew
               .Fields.Refresh
  '********* The assignments to fld do NOT work *********
  'Set fldNew = .CreateField("My_Files", dbAttachment)
               '.Fields.Append fldNew
               '.Fields.Refresh
  'Set fldNew = .CreateField("My_Files", 101)
               '.Fields.Append fldNew
               '.Fields.Refresh
  '******************************************************
End With

MyDB.TableDefs.Append tdfNew
MyDB.TableDefs.Refresh

RefreshDatabaseWindow

This post has been edited by ADezii: Jun 4 2020, 12:48 PM
Go to the top of the page
 
cheekybuddha
post Jun 4 2020, 01:19 PM
Post#28


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


Very strange!

I tested using Acc2k7

EDIT: Your code worked for me in Acc2k7

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jun 4 2020, 02:36 PM
Post#29



Posts: 1,112
Joined: 21-September 14
From: Tampa, Florida USA


Hi ADezii,

Are you able to manually create an attachment field in M365 Access table design?.. If not, then MS deprecated them. In Access 2010, attachment data is not stored in the table it was defined.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
isladogs
post Jun 4 2020, 03:06 PM
Post#30


UtterAccess VIP
Posts: 2,402
Joined: 4-June 18
From: Somerset, UK


For info, I've tested both the code by David (post #26) and both of the attachmentcode types by adezii (post #27) in Access 365
All worked successfully for me.
Not only are the attachment fields created, but so are the related deep hidden system tables associated with the attachment fields-highlighted below
Attached File  Capture.PNG ( 12.31K )Number of downloads: 4


Although I never use any of the 'complex field' datatypes: attachment fields, multivalued fields or memo fields with column history, I would be interested to know of similar code to create either of the latter two

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ADezii
post Jun 4 2020, 03:31 PM
Post#31



Posts: 3,089
Joined: 4-February 07
From: USA, Florida, Delray Beach


OK, got the Code to work in Post# 27 with VBA (CreateField()). It was my stupid mistake that was preventing it. I still, however, cannot create an Attachment Field with DDL, and I'm not sure if it is possible.
Go to the top of the page
 
isladogs
post Jun 4 2020, 03:40 PM
Post#32


UtterAccess VIP
Posts: 2,402
Joined: 4-June 18
From: Somerset, UK


I very much doubt it is possible for the reason I mentioned in my previous reply.

Attachment fields need an associated deep hidden system table to be created at the same time.
I believe DDL will only do what it specifies and so cannot create the associated system table

Similarly with the other complex datatypes.

Having said that, I can't think of a good reason for trying to do so

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
cheekybuddha
post Jun 4 2020, 07:33 PM
Post#33


UtterAccess Moderator
Posts: 13,046
Joined: 6-December 03
From: Telegraph Hill


>> I still, however, cannot create an Attachment Field with DDL <<

I too would be very surprised if it were possible - attachment fields, MVF's etc are not part of standard SQL, and since Access has trouble with creating even basic SQL I think it would be a longshot that they would implement such extensions. tongue.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jun 4 2020, 08:33 PM
Post#34



Posts: 1,112
Joined: 21-September 14
From: Tampa, Florida USA


It's not impossible to implement, it's just that MS has never seen any significant usage of attachment or MVF's. MS did create BIGINT datatypes which are supported in db servers because there was a big demand for them and it made marketing sense. BIGINT support is probably the most significant and only major change I have seen made to Access since 2007's move to accdb's. They would actually be doing Access developers a favor by deprecating attachment and multi-valued datatypes as their implementation was not that good and they're difficult to work with. We never use attachment fields. Storing them externally and referencing their paths is a lot more efficient. You can't export attachments and they quickly bloat the db. In most db servers, complex datatypes such as attachments are stored within the same table they are defined in as BLOB's, memo fields as CLOB's, MVF's as Collections, and Access has no UDF's. However Collections are also a hassle to work with in db servers.
This post has been edited by FrankRuperto: Jun 4 2020, 09:23 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    13th July 2020 - 12:00 AM