Solved

Convert a data type using a macro

Posted on 2004-09-07
10
844 Views
Last Modified: 2010-05-18
Hi All,  
I'm using an Access data base to return all accounts that haven't been loggeg into in over 90 days.  The data is imported from a text file and comes in as text. (most have the last login time as m/d/yyyy h:mm am/pm, but some accounts come in as "Never")

So far I've been changing all the "Never"s to 1/1/1901 using an update query then I've been manually changing the data type in the table definition to date so that my query will run.  This works, and it includes all my "Never" accounts, which is good.

However, I am trying to automate the process.  I want to be able to import the data then just execute my query without having to manually manipulate the data types (this also means that I have to reset them back to text to import data again the next time around.)  And if possible I would prefer to leave everything as text so that I don't have to convert the "Never"s to dates.

Can I do this with a macro, or in VB?

Thanks
0
Comment
Question by:JamesCbury
10 Comments
 
LVL 34

Expert Comment

by:flavo
ID: 12001071
you can use a  querydef to do it

Update tblmyTable Set tblMyTable.myDate = #01/01/1901# where tblMyTable.myDaye = 'Never';
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12001085
A better way would be to import this data into a table where all fields are text.  Then run an update query, changing all instances to "Never" to whatever you like.  Then, append all data from this table into your production table, with the data types you prefer.

In order to pull this off, you'll need to write an Import Spec (File: Get External Data, Import, select your file, then click the 'Advanced' button on the lower left, follow instructions, and make sure to hit 'Save As' and give it a name).  Then, in your Docmd.TransferText statement, specifically state the import spec name.

Hope this helps.
-Jim
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12001178
I second jim's suggestion. That will stop you having to continually change data types.

If you *really* want to do it, you can change the data type from VBA by:
CurrentDb.TableDefs("MyTable").Fields("MyField").Type=dbText

Change dbText to dbDate to change it to a date.
0
 

Author Comment

by:JamesCbury
ID: 12009094
I think that I want to change the fields rather than maintaining another table.  (When I pass this off to another user I want to make it look simple).  

I've set up a macro that will reset all the tables that I am using for a new import, but I can't get that line of code to work when I do a "RunCode" in the macro.  The error reads "Microsoft Access can't find the name 'dbText' you entered in the expression."

Can I run this line in a macro? or do I need to create a module.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12009166
You will need to create a function. You can write one like this:

Function ChangeToText()
CurrentDb.TableDefs("MyTable").Fields("MyField").Type=dbText
End Function

You can call it from your macro with RunCode, and the argument ChangeToText(). Equally, you could do it all from VBA without using a macro at all. You'd need to do a similar thing to change the field back to a date.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:JamesCbury
ID: 12009578
Thanks,

I've got everything set up to call the function.  but I keep getting a error "3219" - Invalid operation.

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12009702
Can you post your function(s)?
0
 

Author Comment

by:JamesCbury
ID: 12009916
This function should clear out my tables and reset the PswdLastSetTime on the User Permissions Dump to text.  This sets up my tables for the next import.

Function Reset()
CurrentDb.Execute ("Delete From [File Sys Dump]")
CurrentDb.Execute ("Delete From [Rights Dump]")
CurrentDb.Execute ("Delete From [Services Dump]")
CurrentDb.Execute ("Delete From [Shares Dump]")
CurrentDb.Execute ("Delete From [User Permissions Dump]")
CurrentDb.TableDefs("User Permissions Dump").Fields("PswdLastSetTime").Type = dbText
End Function
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 350 total points
ID: 12010208
Ah, pardon me. Try this instead of the tabledefs line:

CurrentDb.Execute "ALTER TABLE [Users Permissions Dump] ALTER COLUMN PswdLastSetTime TEXT"

to change it to text, and this to change it back:
CurrentDb.Execute "ALTER TABLE [Users Permissions Dump] ALTER COLUMN PswdLastSetTime DATETIME"
0
 

Author Comment

by:JamesCbury
ID: 12010268
Awsome... That works great
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now