How do I use the SQLBulkCopy Class to transfer data from Excel to SQL server

I have seen a ton of information on using the SQL BulkCopy Class to transfer data from Excel to SQL server but for the life of me I am just not getting it. I have tried to change the code that I have found but to no avail. I am brand new to SQL and I am using the Express edition that comes with the latest version of Visual Studio. Okay here is what I am trying to do. I have an old Access db that I need to transfer to a SQL backend. All of the data that I need to move into the SQL tables are in Excel spreadsheets. I have a SQL table called Collinfo that has 8 columns (QID - primary key - int, CompID - foreign key - int, CollQ - varchar (500), UnAnc - varchar (500), AAnc - varchar (500), EAnc - varchar(500), ExperienceReq - bit, Selected - bit). I need to take the data from an Excel spreadsheet with the exact same column names and blast it into SQL. I have over 500 rows in the Excel files so I don't want to recreate this thing by hand. What I need to know is step by step how do I make this happen. I am new so please leave nothing out - I need to know things as trival as where to paste the code in SQL (e.g. new class or new query) - think trying to teach a monkey to drive a car. Thanks - any help is much appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

zorvek (Kevin Jones)ConsultantCommented:
Here is code I use to dump a table of data into a SQL Server table. It assumes there is no header row and the fields are in the same order. It also assumes the table starts in cell A1 on the active sheet.

Private Const TargetServerName As String = "Server1"
Private Const TargetDatabaseName As String = "Database1"
Private Const TableName As String = "Table1"

Public Sub WriteTable()
    Dim TargetDatabase As ADODB.Connection
    Dim TargetRecordset As New ADODB.Recordset
    Dim Record As Long
    Dim Field As Long
    ' Open target database connection
    Set TargetDatabase = New ADODB.Connection
    TargetDatabase.CursorLocation = adUseClient
    TargetDatabase.Open "Provider=SQLOLEDB;Data Source=" & TargetServerName & ";Initial Catalog=" & TargetDatabaseName & ";Integrated Security=SSPI"
    ' Delete existing records
    TargetDatabase.Execute "DELETE FROM dbo." & TableName
    ' Open target recordset
    TargetRecordset.Open TableName, TargetDatabase, adOpenDynamic, adLockPessimistic
    ' Copy records
    For Record = 1 To ActiveSheet.UsedRange.Rows.Count
        For Field = 1 To TargetRecordset.Fields.Count
            TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)
        Next Field
    Next Record
    ' Close recordset
    ' Close database

End Sub


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jazzjAuthor Commented:

Thanks for the quick response. Where do I put this code in SQL. Do I paste it into a new query?  if not where and how? Also I am assuming I just need to have the Excel spreadsheet open but don't need to specifically reference the Excel file name - is that correct? Does the following look correct for my specific situation"

"Server 1" = RYN\sqlexpress
"Database1" = Collinfo.dbo
"Table1" = Collinfo_T

Thanks for the help.

zorvek (Kevin Jones)ConsultantCommented:
The code is VBA code and is placed in the Excel workbook, not SQL Server.

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

jazzjAuthor Commented:
That explains alot. Okay I ran it and it gives me the following error message.

Compile Error: User defined type not defined
it indicates the Dim TargetDatabase As ADODB.Connection line
jazzjAuthor Commented:

Scratch that. I selected the activeX library from the references and that seem to take care of the problem. Now the error I get is this:

Multiple Step Operation Generated Errors. Check each Status Value.
jazzjAuthor Commented:
This is the line where the error occurs:

TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)

I have checked my data types in SQL and make sure they match so not sure where to go from here.
jazzjAuthor Commented:

you're awesome. Your code worked perfectly. A couple things I needed to do in SQL to get it to work:

- delete my primary designations and reset the identity specification
- change some data types for text data
Was just curious if  anyone has sample code that does this same thing - but in DAO instead of ADO?  And from an Access 2007 database instead of Excel?   I'm just needing  to load a table or query that is in Access to a table in the SQL Server but with speed of the SQLBulkCopy class.   The conventional Access append query is just too slow.  And 'Pass-Through' Append queries will not work with Access queries or tables when your attempting to append data from them.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.