Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

text file time conversion error

Posted on 2013-12-08
4
Medium Priority
?
374 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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