text file time conversion error

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
developingprogrammerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
als315Connect With a Mentor Commented:
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
 
als315Commented:
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
 
developingprogrammerAuthor Commented:
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
 
developingprogrammerAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.