Solved

text file time conversion error

Posted on 2013-12-08
4
350 Views
Last Modified: 2013-12-09
hi guys,

i'm trying to import this text file into Access 2007 using get external data. however the time fields are not imported successfully

Question --> 1) why is it not being imported successfully? opening the text file in excel converts this properly
2) how can i import this text file into Access without using Excel as a go between?

importWork-Hours-27.05.2013IMPORT.txt
0
Comment
Question by:developingprogrammer
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39705521
I always prefer not to import files directly to tables. If you link file at first and then import it with query, you can control process and make conversions. Look at sample (correct path to linked file with linked tables manager)
DBimport.accdb
0
 

Author Comment

by:developingprogrammer
ID: 39705558
hi als315! yes that definitely is an excellent idea! i have always been creating temp tables to do that but your method of linking is so much better - doesn't bloat the database. never knew i could do that before ha.

Question --> 1) hey could you share with me the steps how you manage to link the text file properly? when i tried to link it what i got was this with everything all jumbled up.
2) how can i do the same in code?

thanks in advance als315!

link to text file
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 39705576
I have now russian version of Access and can't show exact steps. Here:
http://office.microsoft.com/en-us/access-help/import-or-link-to-data-in-a-text-file-HA001232227.aspx
near the end of article you can find nesessary steps. Do not forget to set type of all fields to text (with Advanced button) - in this case you will have no type errors.
You can link file once manually  and then relink it with sub like this:
'RelinkTables...Just as the name suggests pass a path to a database to this 
sub 
'eg RelinkTables("c:\windows\test.mdb") 
'and it will go through all the tables in your 
'database and link them to the new location 
'Written by John Hawkins 20/9/99 www.fabalou.com 
Public Sub RelinkTables() 

Dim Dbs As Database 
Dim Tdf As TableDef 
Dim Tdfs As TableDefs 
Dim NewPathname As String 
Set Dbs = CurrentDb 
Set Tdfs = Dbs.TableDefs 
Screen.MousePointer = 11 
NewPathname = Forms!frmLinkTables.txtFilePath 
'Loop through the tables collection 
For Each Tdf In Tdfs 
If Tdf.SourceTableName <> "" Then 'If the table source is other 
than a base table 
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source 
Tdf.RefreshLink 'Refresh the link 
End If 
Next 'Goto next table 
Screen.MousePointer = 0 
MsgBox "All the application tables have been relinked to the database at " 
& NewPathname & " Thank you...", vbInformation, "Tables Reinked" 
End Sub 

Open in new window

0
 

Author Comment

by:developingprogrammer
ID: 39706545
great!! thanks so much als315!! that worked like a charm!! now i have one more problem but i'll post it as another question cause it's related but not exactly as per this question's header = ) thanks als315 once again!! = ))
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

747 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

13 Experts available now in Live!

Get 1:1 Help Now