VBA to export large tables from Excel to SQL Server

I have spreadsheets that I distribute to users.  I want Excel VBA to export 
large tables (2000+ rows) into a remote SQL Server 2005.  I am able to 
successfully do this with the following generic code, but it is too slow:

	Dim con As ADODB.Connection
	Set con = New ADODB.Connection
	con.Open "Driver={SQL 
Server};SERVER=<server>;DATABASE=TestSample;UID=<id>;PWD=<pw>;"
	con.Execute "INSERT INTO ... SELECT * FROM ..."      'Looping this for each 
row
	
It takes over 10 minutes because it does it one line at a time.  

I've also tried looping  "ADODB.Recordset.addnew" ending with 
".UpdateBatch", but it also is too slow, seems to still only be able to 
transfer the table data one row at a time (unless I'm doing something wrong). 
 

Is it really true that VBA/ADO can only export one row at a time?  
Is there some other way to improve performance (send the whole data range at 
one time)?  For example, send the entire table to a SQL Server stored 
procedure or BCP (whatever that is).  If so, how would it work?  Is there a 
way to write the stored procedure generic enough to accept a variety of 
tables, for example by receiving parameters indicating what SQL table they go 
into and if it replaces or appends existing data?

-- 
Thank You!
0
Utf
4/26/2010 5:14:04 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
13213 Views

Similar Articles

[PageSpeed] 34

I've uploaded a pretty large database with the following code and it
didn't take a long time.  See macro below

You could export your spreadshet as CSV and then import the data into
the SQL server in CSV format.


Sub Submit()
'filename of database is with MakeDatabase macro

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Set sht = ThisWorkbook.Sheets("USA")

strdb = Folder & FName

If Dir(strdb) = "" Then
MsgBox ("Database Doesn't Exists, Create Database" & strdb)
MsgBox ("Exiting Macro")
Exit Sub
End If

ConnectStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Folder & FName & ";" & _
"Mode=Share Deny None;"

cn.Open (ConnectStr)
With rs
.Open Source:="USA", _
ActiveConnection:=cn, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

If .EOF <> True Then
.MoveLast
End If
End With


LastCol = sht.Cells(1, Columns.Count).End(xlToLeft).Column

LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
With rs
.AddNew
!ID = sht.Cells(RowCount, "A")
For ColCount = 2 To LastCol
If Data <> "" Then
ColName = sht.Cells(1, ColCount)

rs(ColName) = sht.Cells(RowCount, ColCount)
End If
Next ColCount
.Update
End With
Next RowCount


Set appAccess = Nothing
End Sub


-- 
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198561

http://www.thecodecage.com/forumz

0
joel
4/26/2010 9:28:56 PM
Reply:

Similar Artilces:

Excel 2007
When I select cells to copy as a picture in Excel 2007, the resolution is terrible. Text and objects with shadow's are very blotchy when pasting the picture. How do you change the resolution of a 'Copy Picture'? ...

removing replicated table information from MS Office Access 2003
I have a tool that exports .xml files which are then imported into a table. If I import the file without any replicated information (ie. s_ColLineage, etc) how would this affect the database? Is it safe to do this? Would I be better off just making the tables not replicable? Thanks On Tue, 4 Mar 2008 03:33:07 -0800, DukeJon <DukeJon@discussions.microsoft.com> wrote: Assuming your Access database does not need replicating, yes, it is safe to omit those fields during import. -Tom. >I have a tool that exports .xml files which are then imported into a table. >If I import the f...

SQL deadlock problem
I am currently having a big issue with sql deadlocking on the PrincipalObjectAccess table. The last few months I have been working on a synchronization process using a Biztalk orchestration. The sync uses the crm webservices to create and update account and contact records in CRM. But now deployment to the production environment gives me some problems. It seems that when trying to update account records (which is one of the first actions in the sync process) the webservice gives me Generic SQL errors and SQL timeouts. After extensive profiling and tracing in SQL I found that there are...

Input Excel 'Password to Open' through control in access form
Hi All, We know,Excel has prompt password to open it files. Is it possible to create a code that can supplies the excel prompt password?.So that when we open the excel file through our access control in a form, the excel files can be opened automatically.But when the excel files opened from its default icon,it will prompt a password first. ...

Auto-format in Microsoft Excel.
Each time i try to enter a number range, for example, 8- 10, in Excel, it constantly re-formats it to a date. If I change the formatting to "General" it turns it into a random number, usually 38209. I would like to turn off ALL auto-formatting, but that's probably asking too much. How do I disable this frustrating feature? thanks. Hi Paul When you enter "8-10" in a cell, that's not strictly a number but Excel thinks you mean a date so converts it as such. A date is a number and in your example the number 38209 represents August 10 2004 which is not a ra...

"The connection to the Exchange server is unavailable. Outlook must be online or connected to complete this action"
I have setup a number of user accounts and the relative mailboxes without any problem. However, since this afternoon, if I try and connect to a newly mailbox through the email accounts wizard I get the following error message: "The connection to the Exchange server is unavailable. Outlook must be online or connected to complete this action". This also occurs when I go to File>Open>Other User's Folder... and I try and lookup an existing account/mailbox that I know I have permission to. I know Outlook is connected to the Exchange server (I can ping, send and receive emails) ...

Inserting form values into a table
We have a form with values taken from an sql query that comes from two different tables. We would like to enter the information into a third table. Can some one direct me to code that will do the following: 1. Provide the Insert sql that shows us how to add the form values to the table 2. Show us how to loop while inserting the information into the table (there could be several lines on the form, each must be inserted one at a time). I have worked with Access before and have never had a problem inserting information. However, I cannot quite figure out how to insert informtion through an ...

Excel not Access
I have designed an Access database that holds records relating to my stores audit results going back for about 5 years plus a load more information relating to these stores. This was used to produe a pack once a month, however a change in senior management means that I have got to shelve this and prodce a similar pack in Excel. The idea would be that the user could select a month or a 12 mnth date range that would produce data that could then be used to populate a number of excel templates that have been designed. Having not used excel for years I would be grateful for any suggestion...

How to keep format when importing Excel into SQL.
Can anybody help me please? When I import Excel file into SQL, a field that formated as 0000000000 (custom), loosing leading zeros. I tried to change data type in SQL after importing. No luck. I appretiate any help. See if using IMEX=1 helps: http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html -- Plamen Ratchev http://www.SQLStudio.com Plamen, I tried this, but got an error: "The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered." Thank you very much for looking into this. "Plamen Ratchev" wr...

Unexpected error message on closing an Excel file
Suddenly I am getting the following message when I try to close a workbook: "Your formula contains an invalid external reference to a worksheet. Verify that the path, workbook, and range name or cell reference are correct, and try again" The mysterious thing is that it does not happen consistently and that, after I click OK after the above message, I can still save the file. What might be the cause of this error message and can the "invalid reference" be tracked down using one of the utility add-ins such as J. Walkenbach's PUP? If it only happens when you close ...

cant start outlook pst file to large
Hi I hope somebody may be able to help im new to outlook and now I cant start outlook message says cannot open your default email folders pst file its too large as I cannot open outlook I cannot delete old folders See if this helps: http://support.microsoft.com/kb/296088 -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4law-amazon "Ray" <Ray@discussions.microsoft.com> wrote in message news:B3B824DC-3C9B-4B1B-8EF3-61...

help with simple maths in excel
I want to do a simple arithmatic excercise in excel for my grand daughter. It is stuff like 2+2 =4 Smart me has hit a problem at the first hurdle... I need to put 2 in one cell the + in another cell and then 2 in another = in another and then she puts the answer in the next one. So the above would have 4 cells completed and she would put the answer in the 5th one. When I use the + or = sign in a cell of its own it (excel) thinks I am doing an equation, is there a way around this? I will work on the answer like if she gets it correct or wrong how I will do that...a sound or som...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...

Opening Excel Workbooks
I'm running into an issue where if I click on an Excel file through My Documents, it doesn't automatically bring it up. I get the toolbar but the actual spreadsheet doesn't appear on the screen. I have to click on the taskbar to get it to pop up. If I already have Excel active and I open a file through Excel, this doesn't happen. Any ideas? Here is a similar thread: http://www.excelforum.com/showthread.php?s=&threadid=237195 Rolli -- Message posted from http://www.ExcelForum.com Hi, Take a look at Tools-Options-General tab- uncheck ignore other application...

How to restore entire server?
This is a long post. Its that time of year again where I practise some disaster recovery skills. I set up a completely seperate physical network from the production network. Its got one Win2000 server (DC + all the Operations Master roles, DNS, WINS, etc) with SP4 (different AD/DNS root forest) and running Exchange 2000 with SP3 (same org name as production server, admin group, storage group, logical and physical database names, legacyexchangeDN, etc). As a result, I can successfully restore our production Exchange 2000 w/SP3 Storage Group/Information Store database without problems. Outlook...

How To Copy MS Word mailing labels into Excel
I have a word doc that I want to put into Excel. I want to add some more fields to the names and addresses. Is this simple or do I have to learn how to program? Michael Rodriguez City of Grand Prairie Michael, have you tried to copy and paste the data into excel? -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2000 & 97 ** remove news from my email address to reply by email ** "Michael Rodriguez" <mrodrigu@gptx.org> wrote in messa...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

Install Exchange 2003 Server on new Hardware with same name
I got a new server to move my Exchange Server Installation. I want to keep the name I'm using now for my Server. Already read TechNet Article "How to Move Exchange Server 2003 to New Hardware and Keep the Same Server Name". Is there anything else that I should be aware of that is not on this TechNet article? I'm using a different Disk Configuration, does this matters? Should this be a smooth process? Thanks for any help you can give. The drive letters MUST match, because the paths of the databases are at the Active Directory. The disks can have different sizes than the or...

Mails not appearing in Inbox although on Server and viewable by colleague
Hi all, I have searched the archives and found a few more or less related topics to my problem, but unfortunately none of them helped. Here's my problem: I know that I recieve several mails at a specific time each day. Those mails are sent to a colleague of mine and myself but only he recieves them. My admins looked on the Exchange Server and all say, that they can definetely see these emails in the server logs and that the server delivered them to the right mailbox. If my colleague looks into my mailbox before(!!) I open Outlook, he sees these mails as unread in my inbox. As soon as I op...

Excel DNS query
Hello, Is there a way for an Excel function to query a DNS server? Thanks. Soundy Not that I know of, but you can turn on the macro recorder, use 'get external data' and tailor the resulting code into a user function of your own. E.g. I've used this to create a button to get MS-Access data from a query that has the same name as the sheet (tab) name. It saves me a lot of copy-paste actions. Bas Hartkamp <soundy@gmail.com> schreef in bericht news:1151940450.029823.127570@j8g2000cwa.googlegroups.com... > Hello, > > Is there a way for an Excel function ...

[b]Can I download Excel data to a MS Access database?[/b]
I've built an Excel 2002 form that I want our internal customers to access from our intranet, and use. Once completed, they will send it to us as an e-mail attachment. I'd like to be able to open it, and somehow download the data from the form into an MS Access 2002 database I've built (so that we don't have to rekey it into the database). Is this possible or even feasible? Any and all help is appreciated. Thanks. :D --------- Message sent via www.excelforums.com Hi in Access check 'File - Import External data' -- Regards Frank Kabel Frankfurt, Germany "...

Excel Problem
I have a 23.8 meg excel 2000 spreadsheet set for manual calculation saved to my local hard drive. Every time I try to open it, it takes forver and sometimes never opens but I do not get any error messages, let me just tell you that I am running a P4, 1 GB memory, Office 2K with SP3, and nothing else running when I try to open it. As I said it is set for manual calculation, and it is cleared to not auto calculate when opening or closing. Any idea's as to why this is happening? -- Todd I don't know why you're having this problem but I would like to point something out for w...

Weighted Average Calculations for Pivot Tables
How does one implement weighted averages in pivot tables. Assume Bonds Interest Rate 100,000 .06 200,000 .04 800,000 .10 Weighted Average Interest Rate is .0927 (I normally do this using sumproduct(col a,col b)/sum(col a) I would like to be able to perform this in a pivot table with the weighted average calc being a grand total. You can see how this can be useful and expanded once the basics are found. Any ideas. ffrazz ...

Please Help w/ Large Excel File
Hello everyone. I have this Excel file that has drawing text box objects in the document. When I delete a text box the attributes still are present, but just not showing. It is continually growing my file size. When I export the document to HTML after deleting all the text box drawing objects and saving the file, the text box objects still show as vtextbox elements in this file. I tried to reproduce this with a new Excel workbook but could not. I would be greatful if anyone can figure this out, as I have a lot of documents to re-create if there is no solution. Thank you. exce...