Import text file specification

[toekneel]

I'm working with Microsoft Access 2000 Project (*.adp) as the front end of a SQL Server 2000 db. I need to import a text file with import specifications, which was easily done in *.mdb. Problem is that the *.adp does not allow you to use the same interface to establish your specs. The TransferText command is looking for a schema.ini file to use as the import specification, housed in the same folder as the *.adp file. I find no such file in searches of existing Access 2000 file areas. I need to find what that schema should look like so I can build it and then utilize it in importing my text file. Any help from SQL Server gurus who discovered that moving to SQL Server from Access just wasn't quite as easy as you'd always been told?

Thanks!

Tony L

 

[ChrisCalvert]

I am only a beginner at this stuff, however I know that the import specification is not saved separately but it is a part of the *.mdb file. You might try creating a schema with the get external data and advanced in access. Name it something wild like zzzxxxqqq and try searching around and trying to mine it out of the mdb file. Maybe an easier way..probably an easier way. Just thought I'd mention it.

 

[toekneel]

In Access 2000 (.mdb) there are system tables which store the specifications. I've tried copying and pasting those tables into my (.adp) file but with no impact.

 

[toekneel]

I ended up taking a different approach to solving this problem. I couldn't find a text import specification, but I found a way to work around it.

I set the TransferText to work without using import specs by setting up an intermediate transfer table without specifying any field names.

DoCmd.TransferText acImportDelim, , "tmpTable", "C:\TextFile\File.txt", False

From this tmpTable, I then ran a stored procedure within SQL Server to append that tmpTable to the working table in the system. My proc used Select into commands where I set "tmpTable.F1 as workingtable.fieldname" for each field in the table.

I also needed to delete the rows out of my tmpTable so they wouldn't get duplicated with future updates. I set that up in the end of the proc ("DELETE tmpTable&quot.

Tony L