473,320 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Changing Data/type in table with Module

What I need to do is change the datatype in my table from text to date/time programatically. I have to run this several times a day so I am writing a macro to do it for me.

Thanks for all your help!!!
Sep 24 '07 #1
2 1193
I got it figured out.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. 'The AlterFieldType Sub procedure requires three string parameters with an
  3. 'optional fourth. The first string specifies the name of the table
  4. 'that contains the field to be changed. The second string specifies the
  5. 'name of the field to be changed. The third is the data type of the field.
  6. 'The fourth and optional parameter holds the size value when you specify
  7. 'binary data types and text data types.
  8.  
  9. Sub AlterFieldType(TblName As String, FieldName As String, DataType As _
  10.    String, Optional Size As Variant)
  11. Dim cd
  12. If IsMissing(Size) Then
  13. DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
  14.    "] " & DataType
  15. Else
  16. DoCmd.RunSQL "ALTER TABLE [" & TblName & "] ALTER COLUMN [" & FieldName & _
  17.    "] " & DataType & "(" & Size & ")"
  18. End If
  19. End Sub
  20.  
  21.  
  22. Public Function Update_GKNStatus()
  23. DoCmd.SetWarnings False
  24. Screen.MousePointer = 11
  25.  
  26. DoCmd.OpenQuery "qry-getdates"
  27. AlterFieldType "getdates", "Line Unit 0001", "date"
  28. AlterFieldType "getdates", "line unit 9997", "date"
  29. AlterFieldType "getdates", "Line Unit 0002", "date"
  30. AlterFieldType "getdates", "line unit 9998", "date"
  31. AlterFieldType "getdates", "Line Unit 0003", "date"
  32. AlterFieldType "getdates", "Line Unit 0004", "date"
  33. AlterFieldType "getdates", "Line Unit 9901", "date"
  34. AlterFieldType "getdates", "Line Unit 0005", "date"
  35. AlterFieldType "getdates", "Line Unit 0006", "date"
  36. AlterFieldType "getdates", "Line Unit 0007", "date"
  37. AlterFieldType "getdates", "Line Unit 0008", "date"
  38. AlterFieldType "getdates", "Line Unit 0009", "date"
  39. AlterFieldType "getdates", "Line Unit 0010", "date"
  40.  
  41. DoCmd.SetWarnings True
  42. Screen.MousePointer = 1
  43.  
  44. End Function
  45.  
Sep 26 '07 #2
Scott Price
1,384 Expert 1GB
Glad you got it working and thanks for posting the working code!

Regards,
Scott
Oct 5 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Jon Brunson | last post by:
Is it possible to use a DataAdapter to fill a DataTable, change the DataColumns of that DataTable (and maybe even it's name) and then commit those changes to the database (in my case SQL Server...
1
by: iporter | last post by:
Hi - I am changing a field from type nvarchar to type text, given that I need to store strings longer than 255 characters. To do this I change the data type in SQL Server, then I change the...
31
by: Greg Scharlemann | last post by:
Given some recent success on a simple form validation (mainly due to the kind folks in this forum), I've tried to tackle something a bit more difficult. I'm pulling data down from a database and...
13
by: Peter | last post by:
Can anyone tell me how to change the data type of a field in a table created with a make table query? The field is a binary and must be changed to text. alternately does anyone know how to specify...
1
by: Dan | last post by:
Could someone please help me with auto importing a series of data files into an Access table. I tried to follow code given below in a previous messagebut i'm getting error messages. Here's my...
2
by: windandwaves | last post by:
Hi Gurus I have a module with a bunch of functions that tell me everything about a table (e.g. the number of children, whether it exists, the table description, etc..., data-entry quality,...
1
by: Larry Bird | last post by:
I've created a AlertDataClass below within the class I have tables and column that I've create. In the AlertDataAccess class I'm trying to insert data into my tables. AlertDataAccess is a Module...
1
by: rbinington | last post by:
Hi, I am trying to write a DNN module that has the ability to insert articles into an article repository. I want the users to be able to move pages around and enter text into the FCKEditor. I...
3
by: James Mills | last post by:
On Thu, Oct 9, 2008 at 2:26 PM, Warren DeLano <warren@delsci.comwrote: Yes it does :) I second this. It's far better to use Data Structures rather than Programming Constructs
4
by: Mubs | last post by:
Hi ppl, I am trying to insert records into my database using a form. this works. but after this i would like the records to be displayed onto another page.. the database gets updated but the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.