Import into access adp with Pipe deliminator

i have tried to do this on my computer which is running access 2003 and the option to "save as" is greyed out. I presumed it was because i was using an ADP database instead of an MDB however i may be wrong.

Sorry- I'm wrong.
I couldn't think of any reason why this should not work. But apparently it doesn't.
See:
http://support.microsoft.com/kb/290587

i thought it was weird disabling it also.

Is there any way i could do the import using VBA code?

I thought i could i could import it into a table and then read the characters until i reached a  |   save them as a string and then input that string into a column of a table using an insert command? however im not sure how i would script the reading of the row until i reached a  |  then carried on in the next 1 etc or if it is even possible.

You can use a schema.ini file instead.
I've never done this but here are some instructions on how to create one..
http://support.microsoft.com/?kbid=210001

Another version of how-to:
http://www.ehow.com/how_5276833_simplify-access-schemaini-based-table.html

i read through the 2nd one you posted and it says

Design a new table with only one field having a data type of text.
The field is named using the convention of Col1,Col2,Col3,and so on.

The field width property is what you use to define how to break up each line of the imported text file. For example field Col1 with a width property of 25 would hold the first 25 characters, Col2 with a width of 10 would hold next ten characters. So set each field width based on how you want to divide each line or row of the text file.

looking at the layout it would have to be a fixed width?? However the length of the columns can change on my import they are not all a fixed width.

Yes, I see what you mean.
As I said, I've never done this, but I assume that it must be possible to modify the code to deal with delimited files.

It would probably be worth googling something like
adp schema.ini delimited
to see if there is anything out there already.

I have found a way to do it using the split() function

However i have typed the Value in my self.

Is it possible to read the value from a table called tblimport with 1 column called input.

and it would go through the table till it has split off all the entries. so instead of me putting in

Split("504122474|1363454|DAN JAMES|02408829|29/10/2009 20:00:37", "|")

I would have

dim sqlimport as string
sqlimport = "select tblimport.import from tblimport"

which would then pick up 3 entries like so

504122474|1363454|Dan James|02408829|29/10/2009 20:00:37
123|1363455|Mark Johnson|02408830|29/10/2009 20:01:02
14587045|1363456|David Jones|02408831|29/10/2009 20:01:26

So it would then split it and would appear in table tbltest like so

Field 1      Field 2      Field 3      Field 4      Field 5
504122474      1363454      Dan James      2408829      29/10/2009 20:00
504122482      1363455      Mark Johnson      2408830      29/10/2009 20:01
166287045      1363456      David Jones      2408831      29/10/2009 20:01

I believe i would have to do a do while not EOF or something similar however i am unsure how i would code that on top of the pre-existing code?

Dim sqlstr1 As String
Dim sqlstr2 As String
Dim sqlstr3 As String
Dim sqlstr4 As String
Dim sqlstr5 As String
 
 
    Dim avarSplit As Variant
    Dim intIndex As Integer
    avarSplit = Split("504122474|1363454|DAN JAMES|02408829|29/10/2009 20:00:37", "|")
    For intIndex = LBound(avarSplit) To UBound(avarSplit)
         MsgBox "Item " & intIndex & " is " & avarSplit(intIndex) & _
        " which is " & Len(avarSplit(intIndex)) & " characters long", vbInformation
If sqlstr1 = "" Then
sqlstr1 = avarSplit(intIndex)
MsgBox sqlstr1
GoTo skip
End If
 
If sqlstr2 = "" Then
sqlstr2 = avarSplit(intIndex)
MsgBox sqlstr2
GoTo skip
End If
 
If sqlstr3 = "" Then
sqlstr3 = avarSplit(intIndex)
MsgBox sqlstr3
GoTo skip
End If
 
If sqlstr4 = "" Then
sqlstr4 = avarSplit(intIndex)
MsgBox sqlstr4
GoTo skip
End If
 
If sqlstr5 = "" Then
sqlstr5 = avarSplit(intIndex)
MsgBox sqlstr5
GoTo skip
End If
 
skip:
"Update tbltest Set tbltest.[field 1] = sqlstr1, tbltest.[Column2] = sqlstr2, tbltest.[Column3] = sqlstr3, tbltest.[Column4] = sqlstr4, tbltest.[Column5] = sqlstr5"
    Next
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:

Thanks very much.