Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

text file time conversion error

Posted on 2013-12-08
4
Medium Priority
?
383 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 40

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 40

Accepted Solution

by:
als315 earned 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

877 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