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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Change Datatype (createproperty) By Vba, Access 2013    
 
   
MikeGSuppe
post Nov 13 2019, 11:13 PM
Post#1



Posts: 145
Joined: 3-July 10
From: australia


Greetings

I am importing a spreadsheet into access. I have established how to change the fieldnames by the following code;

CurrentDb().TableDefs("tbl_Import").Fields(0).Name = "VendorID"

I now want to amend the datatype to say Number - Double

Hence my code could be??
CurrentDb().TableDefs("tbl_Import").Fields(0).CreateProperty ???

Or am i barking up the wrong tree?

(I have done a search on this but cannot seem to find what I want.)

Thanks

~mike

Go to the top of the page
 
theDBguy
post Nov 13 2019, 11:49 PM
Post#2


UA Moderator
Posts: 76,840
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. Have you looked over here yet. If it's not there, maybe you could try DDL.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Nov 13 2019, 11:51 PM
Post#3



Posts: 1,012
Joined: 25-January 16



Could use action SQL:

CurrentDb.Execute "ALTER TABLE tbl_Import ALTER COLUMN VendorID DOUBLE"

@theDBGuy, that link errors.

This post has been edited by June7: Nov 13 2019, 11:52 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
theDBguy
post Nov 14 2019, 12:05 AM
Post#4


UA Moderator
Posts: 76,840
Joined: 19-June 07
From: SunnySandyEggo


Sorry, I think it's fixed now. And thanks for posting the DDL option.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
June7
post Nov 14 2019, 01:13 AM
Post#5



Posts: 1,012
Joined: 25-January 16



Yes, link worked.

Apparently DDL (ie, action SQL) is only way to programmatically change this property after field is created.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
ADezii
post Nov 14 2019, 01:25 PM
Post#6



Posts: 2,703
Joined: 4-February 07
From: USA, Florida, Delray Beach


Actually, you can pro-grammatically change the Data Type of a Field once it has been created using DAO, but it is hardly worth the effort and is a multi-step process. I am making the dual assumptions that the [VendorID] Field may/may not be Indexed and if it is Indexed, then The Name of the Index is the Name of the Field ('VendorID'). I won't bore you with details, but simply post the Code:
CODE
Dim MyDB As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

Set MyDB = CurrentDb
Set tdf = MyDB.TableDefs("tbl_Import")

'Cannot DELETE a Field if it is Indexed, so if it is, DELETE the Index
For Each idx In tdf.Indexes
  If idx.Name = "VendorID" Then tdf.Indexes.Delete idx.Name
    Exit For
Next

Set fld = tdf.CreateField("VendorID_New", dbDouble)     'Create New Field (DOUBLE)
tdf.Fields.Append fld

'Copy Data from [VendorID] to [VendorID_New]
MyDB.Execute "Update tbl_Import SET [VendorID_New] = [VendorID]", dbFailOnError

'DELETE Old Field
tdf.Fields.Delete "VendorID"
tdf.Fields.Refresh

'Rename New Field to Old
tdf.Fields("VendorID_New").Name = "VendorID"
tdf.Fields.Refresh

MyDB.Close
Set MyDB = Nothing
Set tdf = Nothing
Set fld = Nothing

P.S. - Code has been tested and is operational.
This post has been edited by ADezii: Nov 14 2019, 01:27 PM
Go to the top of the page
 
June7
post Nov 14 2019, 03:18 PM
Post#7



Posts: 1,012
Joined: 25-January 16



So not really changing the property - this is creating a new field with correct property, updating new field, deleting old field, renaming new field. Yes, I thought of this approach and as you said, not worth it.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
MikeGSuppe
post Nov 14 2019, 07:31 PM
Post#8



Posts: 145
Joined: 3-July 10
From: australia


thank you for all the ideas - have tried the code from June7 and it does help out in this situation

Thank you again

~mike
Go to the top of the page
 
theDBguy
post Nov 15 2019, 12:35 PM
Post#9


UA Moderator
Posts: 76,840
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    9th December 2019 - 06:13 PM