asked on

Import text file from share drive into SQL Server Express 2008

I am in the process of migrating from Access tables to SQL Server Express (yay!) that is running on a local machine.

I have a text file that gets imported by my users through the Access import process, but now I need to be able to import the text file (tab delimited) into the SQL server.

Users are used to being able to save the file on a shared network drive then select the file using a dialog box.

How can I accomplish this in SQL Server?

omgang

One option is to continue to use Access to perform the data import. Link the SQL Server tables into an Access db. An advantage to this is no change for your users.
OM Gang

wipnav

ASKER

So import the data into a local table in the Access file then run an insert statement to the linked SQL table?

ASKER CERTIFIED SOLUTION

omgang

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

wipnav

ASKER

Yes I do,

I was hoping to run the imort and update process in a stored procedure on the server called from Access. I was hoping to get the full filename from Access and pass it as a parameter to the SQL procedure.

omgang

Seems like that should be doable but I don't have any experience doing so. I'm sure another expert will chime in here shortly with some suggestions.
OM Gang

omgang

Here's a PAQ on calling a SQL SP from Access and passing parameters. https://www.experts-exchange.com/questions/23311019/Access-2003-adp-and-SQL-Server-2000-connection-issues.html?sfQueryTermInfo=1+10+adraughn+changesql+pass+thru

OM Gang

wipnav

ASKER

This solution is the only option I have at this time.

I would have liked to share a folder on the C drive of the SQL machine but the IT department at my company won't allow it =/

Thank you for your help!