Data export from text file to SQL Server DB DXC 30.11.2005 20:41:09 Is there a way to to import text files to SQL server database table(s) using MS Access ? If so, how ?. I know that the text files can be imported to SQL Server database table(s) without using MS Access but we have people who are not familiar with SQL Server at all and we are trying to get them to be familiar with it. Thanks for any help.

Re: Data export from text file to SQL Server DB "Kevin3NF" <KHill[ at ]NopeIDontNeedNoSPAM3NF-inc.com> 30.11.2005 22:59:04 If the structure is the same, I would create a DTS package and a stored procedure that calls it. have them click a button that send the file name, etc to the SP. HTH, -- Kevin Hill President 3NF Consulting www.3nf-inc.com/NewsGroups.htm "DXC" <DXC[ at ]discussions.microsoft.com> wrote in message news:2C51A855-07C9-4835-A52A-3C8035EAC67C[ at ]microsoft.com... [Quoted Text] > Is there a way to to import text files to SQL server database table(s) > using > MS Access ? If so, how ?. I know that the text files can be imported to > SQL > Server database table(s) without using MS Access but we have people who > are > not familiar with SQL Server at all and we are trying to get them to be > familiar with it. > > Thanks for any help.

Re: Data export from text file to SQL Server DB "Jesper Fjølner" <askfor[ at ]myemail.com> 01.12.2005 04:15:07 [Quoted Text] > Is there a way to to import text files to SQL server database table(s) > using > MS Access ? If so, how ?. I know that the text files can be imported to > SQL > Server database table(s) without using MS Access but we have people who > are > not familiar with SQL Server at all and we are trying to get them to be > familiar with it. If you have a table in Access linked to SQLserver via ODBC then you can probable use the regular Text Import Wizard.

Re: Data export from text file to SQL Server DB DXC 01.12.2005 14:30:05 Yes. That is exacly what we are trying to do. Use link table(s). But we are trying to automate the process. Click of a button, load the data into SQL Server tables. How is this possible with text import ? Thanks. "Jesper Fjølner" wrote: [Quoted Text] > > Is there a way to to import text files to SQL server database table(s) > > using > > MS Access ? If so, how ?. I know that the text files can be imported to > > SQL > > Server database table(s) without using MS Access but we have people who > > are > > not familiar with SQL Server at all and we are trying to get them to be > > familiar with it. > > If you have a table in Access linked to SQLserver via ODBC then you can > probable use the regular Text Import Wizard. > > >

Re: Data export from text file to SQL Server DB "Jesper Fjølner" <askfor[ at ]myemail.com> 01.12.2005 16:18:33 [Quoted Text] > Yes. That is exacly what we are trying to do. Use link table(s). But we > are > trying to automate the process. Click of a button, load the data into SQL > Server tables. How is this possible with text import ? Have you tried the Docmd.TransferText command? I think that's the fastest option. This also works. I'm assuming a file "c:\1.txt" with three numbers per line seperated by a space as: 5 6 7 7 8 9 12 23 56 and then this should work (but is not the fastest way I think). Dim intFileNo As Integer Dim strLine As String Dim arr() As String Dim db As Database intFileNo = FreeFile() Open "C:\1.txt" For Input As #intFileNo Set db = CurrentDb ' Læs alle linjer 'Begintrans 'optional may speed it up Do While Not EOF(intFileNo) Line Input #intFileNo, strLine arr = Split(strLine, " ") db.Execute ("INSERT INTO tablename (f1,f2,f3) VALUES (" & arr(0) & "," & arr(1) & "," & arr(2) & ")"), dbFailOnError Loop 'CommitTrans - optional may speed it up Erase arr Close #intFileNo Jesper Fjølner

Re: Data export from text file to SQL Server DB DXC 01.12.2005 18:19:03 Thanks for all the help.......... "Jesper Fjølner" wrote: [Quoted Text] > > Yes. That is exacly what we are trying to do. Use link table(s). But we > > are > > trying to automate the process. Click of a button, load the data into SQL > > Server tables. How is this possible with text import ? > > Have you tried the > Docmd.TransferText command? > > I think that's the fastest option. > > This also works. I'm assuming a file "c:\1.txt" with three numbers per line > seperated by a space as: > 5 6 7 > 7 8 9 > 12 23 56 > > and then this should work (but is not the fastest way I think). > > Dim intFileNo As Integer > Dim strLine As String > Dim arr() As String > Dim db As Database > > intFileNo = FreeFile() > Open "C:\1.txt" For Input As #intFileNo > Set db = CurrentDb > ' Læs alle linjer > 'Begintrans 'optional may speed it up > Do While Not EOF(intFileNo) > Line Input #intFileNo, strLine > arr = Split(strLine, " ") > db.Execute ("INSERT INTO tablename (f1,f2,f3) VALUES (" & arr(0) & "," & > arr(1) & "," & arr(2) & ")"), dbFailOnError > Loop > 'CommitTrans - optional may speed it up > Erase arr > Close #intFileNo > > > > Jesper Fjølner > > >

Re: Data export from text file to SQL Server DB "Jesper Fjølner" <askfor[ at ]myemail.com> 01.12.2005 23:02:11 [Quoted Text] > Thanks for all the help.......... You're welcome. Did it work?