Solved

text file time conversion error

Posted on 2013-12-08
4
365 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 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

615 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