rufus_the_tailgunner said:
Thanks, that creates the schema.ini file OK, but now I'm getting new errors !
If I do DoCmd.TransferText acImportDelim,"schema.ini", Tablename, Filepath,
True (where Tablename and Filepath are string variables as the names suggest)
then I get error 3625 : The text file specification 'schema.ini' does not
exist. Etc...
So I looked around the other messages on this board and found John Nurick's
reply to Allen_N on 22/1/07 which suggested leaving the spec out of the DoCmd
statement. (i.e. DoCmd.TransferText acImportDelim,, Tablename, Filepath,
True) However, if I do that, I'm back to the same problem I raised
originally, which suggests that the schema.ini file isn't being used.
(Just to confirm also, the schema.ini file, the import (.txt) file, and the
database are all in the same dir, and I'm working on Access 2000 using the
v9.0 Object Library)
Could this be anything to do with the fact I'm using tab-delimited files &
not fixed length ?
Thanks again.
Rufus
OK, looks like I've answered my own question - discovered Q241477 in MSDN
which documents an Access 2000 error when trying to use a schema.ini to
import with the transfertext method, advising the use of DAO db.Execute
method.
So just for info, what I did was :
'....
strSQL = "SELECT * INTO " & Tablename & " FROM
[Text;FMT=Delimited;HDR=Yes;DATABASE=" & dbFilepath & "].[" & Tablename &
"#txt];" & ""
'.....Here I've had to drop the destination table because I haven't worked
out the syntax for an INSERT statement yet
'.....but that doesn't matter as I want to overwrite any existing data anyway
db.Execute strSQL, dbFailOnError
'....and so on
Thanks again for the help with schema.ini
Rufus
Glad you got it worked out.
Just as an FYI, I use this method to link to delimited text files. The files
themselves can be huge so I decided not to import them. Below is the code I use
to :
1. Write a script
2. Copy a file from an FTP server to a local folder
3. Delete the target table if it exists
and
4. Link to the text file using the schema.ini file
As always, watch for line wrap.
Regards,
RD
<code>
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : fCreateScript
' DateTime : 6/11/2007 14:52
' Author : RD
' Purpose : Creates a script file for the FTP function
'---------------------------------------------------------------------------------------
'
Function fCreateScript(sFolder As String, sFileName As String) As Boolean
Dim lFileNumber As Long
On Error GoTo ErrHandler
lFileNumber = FreeFile ' Get unused file
number.
Open "C:\Jobs\WTWscript.scr" For Output As #lFileNumber ' Create file name
Print #lFileNumber, "lcd " & """C:\Jobs\Extracts""" ' Output text
Print #lFileNumber, "open 10.21.16.112" ' ftp server
Print #lFileNumber, "anonymous" ' user
Print #lFileNumber, "(e-mail address removed)" ' generic password
Print #lFileNumber, "cd " & sFolder ' change to the
proper directory
Print #lFileNumber, "get " & sFileName & " " & sFileName ' copy file over
Print #lFileNumber, "bye" ' quit ftp session
Close #lFileNumber ' Close file.
fCreateScript = True
ExitPoint:
On Error Resume Next
Exit Function
ErrHandler:
Debug.Print Err.Number & ": " & Err.Description
fCreateScript = False
Resume ExitPoint
End Function
'---------------------------------------------------------------------------------------
' Procedure : fFTP
' DateTime : 6/11/2007 14:50
' Author : RD
' Purpose : Copies extract files from the ftp server to a local folder
'---------------------------------------------------------------------------------------
'
Function fFTP(stSCRFile As String) As Boolean
Dim stSysDir As String
On Error GoTo ErrHandler
stSysDir = Environ$("COMSPEC")
stSysDir = Left$(stSysDir, Len(stSysDir) - Len(Dir(stSysDir)))
Call Shell(stSysDir & "ftp.exe -s:" & stSCRFile, vbNormalFocus)
DoEvents
fFTP = True
ExitPoint:
On Error Resume Next
Exit Function
ErrHandler:
Debug.Print Err.Number & ": " & Err.Description
fFTP = False
Resume ExitPoint
End Function
'---------------------------------------------------------------------------------------
' Procedure : fImportExtract
' DateTime : 6/13/2007 13:42
' Author : RD
' Purpose : Actually, it creates a table *linked* to the text file
' : It works very well and keeps the size of the database down
'---------------------------------------------------------------------------------------
'
Public Function fImportExtract(sExtract As String) As Boolean
On Error GoTo ErrHandler
Dim db As DAO.Database, tdTblDef As DAO.TableDef
' Delete the table if it already exists
If fTableExists(sExtract) Then DoCmd.DeleteObject acTable, sExtract
Set db = CurrentDb
Set tdTblDef = db.CreateTableDef(sExtract) ' Create the table
tdTblDef.Connect = "TEXT;Database=C:\Jobs\Extracts;table=" & sExtract & ".TXT"
' Connect it to the text file
tdTblDef.SourceTableName = sExtract & ".TXT" ' Name the source file
db.TableDefs.Append tdTblDef ' Append the new table to the table collection
db.TableDefs.Refresh ' Refresh the table collection
fImportExtract = True
ExitPoint:
On Error Resume Next
Set db = Nothing
Exit Function
ErrHandler:
If Err.Number = 3265 Then Resume Next
Debug.Print Err.Number & ": " & Err.Description
fImportExtract = False
Resume ExitPoint
End Function
'---------------------------------------------------------------------------------------
' Procedure : fTableExists
' DateTime : 6/13/2007 13:46
' Author : RD
' Purpose : Checkes to see if a given table already exists
'---------------------------------------------------------------------------------------
'
Function fTableExists(sTblNm As String) As Boolean
Dim db As DAO.Database, tbl As DAO.TableDef
Set db = CurrentDb
For Each tbl In db.TableDefs
If tbl.Name = sTblNm Then
fTableExists = True
Exit Function
End If
Next tbl
Set db = Nothing
End Function
</code>
