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
5231 Views

Similar Articles

[PageSpeed] 28

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:

How to image Server 2008?
Is it possible to image a Windows 2008 server as a backup strategy? I currently use Norton Ghost to automatically maintain a backup image on a desktop PC, and would like to do the same with a 2008 file server. I tried using Symantec Ghost Solution Suite but am unable to image the host server with it. Am I missing something? "Tom Hill" <tomhill@tech2go.ca> wrote in message = news:ext8se%23uKHA.4752@TK2MSFTNGP04.phx.gbl... > Is it possible to image a Windows 2008 server as a backup strategy? > I currently use Norton Ghost to automatically maintain a backup i...

VBA code to say Yes or No if any VBA code is present in ActiveWork
Using XL 2003 & 97 Would like hit Ctrl+Shift+V and have an answer either yes or no as to the presence of Any VBA code in the Active Workbook. I do know that about the VBA editor/Explorer or to press the Run Macro button and get a list from various sources . I would like to stay at the keyboard and process a series of macros including one to let me know if I even need to evoke the VBA explorer. TIA Dennis There is no bootstrap macro to tell you whether you have macros or not. If there were there would be no limit to virus writers. --- HTH, David McRitchie, Microsoft MVP - Excel ...

Installing Exchange 2000 on Win Server 2003
Hello All, I need to find out if I can install Exchange 2000 on server that has win server 2003 OS. Thanks Yes, you can. "maxtalebi@hotmail.com" <maxtalebi@hotmail.com@discussions.microsoft.com> wrote in message news:E913A7B6-F7B0-4F2A-8E36-DA2EA950C6F7@microsoft.com... > Hello All, > I need to find out if I can install Exchange 2000 on server that has win > server 2003 OS. > > Thanks This configuration is not supported, because Exchange 2000 does not understand IIS 6.0 which is part of Windows Server 2003. However, I have heard from people who were still ...

Exporting Classes from DLL
This is something I haven't played with before at all so perhaps someone could help me out. I would like to write a DLL in C++ that exposes a class that could be used from either C++ or a .NET language. Is there anything special I need to do to export such a class? Any potential problems? Perhaps there is a Web site that discusses this? Thanks! -- Jonathan Wood SoftCircuits http://www.softcircuits.com Available for consulting: http://www.softcircuits.com/jwood/resume.htm Consult your MSDN documentation. Here are some tips. There are following ways that you can export your classes t...

E2K3 cluster acting as a bridgehead server
Hi all the group; First of all, I've readed somewhere on the Internet that an E2K cluster cannot act -cannot be configured- as a bridgehead server (don't know why but it seems is a limitation when you come from a Exchange 5.5. upgrade) However, in our corporation, we have two E2K3 SP2 clusters (active/passive) and haven't found any problem on configuring them as the bridgehead servers for their own administrative groups (every cluster is located in a different site). These clusters/bridgehead servers are the responsibles ones for sending the mail to our only frontend in the...

Excel Training on the MAC
Can anyone recommend or point me in the direction of any free introductory and intermediate Excel training programs for the MAC available on the web? Thanks in advance. Ian ...

Print, Merged Cells, and VBA
Hello I'm looking for a code that will help me with a print job for a worksheet. There are a lot of merged cells and a lot of "IF"s to be used in the VBA for range selection. Thanks in advance, Sarr ...

Drop Down List in Excel
Hello All, Can someone please tell me if it is possible to create a "dropdown" list in excel? Or is it more appropriate to use Access for this function? Thanks in advance!! Clay Hi see: http://www.contextures.com/xlDataVal01.html -- Regards Frank Kabel Frankfurt, Germany "Clay Rembert" <clay@rembert.com> schrieb im Newsbeitrag news:e5cr13NbEHA.1652@TK2MSFTNGP09.phx.gbl... > Hello All, > > Can someone please tell me if it is possible to create a "dropdown" list in > excel? Or is it more appropriate to use Access for this function? Thanks i...

GL10001 table Issue
Hi, This is the 2nd time within 6-7 months period, we experienced that GL10001 detail file getting truncated. All Header GL10000 records were intact. Do not know what causing it. I think its getting cleared while posting the batches. I cheked the audit log sy05000 even though it says the General Enry batch XXXXXXXX has finished posting but no records were found in GL20000. Is there any way to find out what causing it or where to search for possible log of any kind. Appreciate your help. Thanks Hi, This is intermittently occuring, with the GL Header intact and GL...

Exporting a toolbar
Excel 2003 I've created a personal toolbar, which includes specific canned command buttons, as well as about six "designer" buttons which I created to activate several formatting macros. I'd like to recreate this toolbars, along with the embedded macros, to a different computer. What is (are) the file(s) I should copy over, and in what folder(s) are they? -- PT I think you'l have many fewer problems if you create the toolbar on the fly when the workbook opens (and destroy that toolbar when the workbook closes). For additions to the worksheet menu bar, I rea...

SQL Not Working
What is wrong with this SQL? I am trying to use a Median function and trying to have the median calculate for each JobCode. SELECT tEmployeeMasterCopy.JobCode, (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) AS MedianByJobCode FROM tEmployeeMasterCopy GROUP BY tEmployeeMasterCopy.JobCode; Thanks in advance. It seems that (SELECT Median("tEmployeeMasterCopy","HourlyRate") FROM tEmployeeMasterCopy as M1 WHERE M1.JobCode = tEmployeeMasterCopy.JobCode) ...

Use VBA to update Access table or Query from Excel
Can I use VBA to update Access table or Query from Excel? Thanks in advance Hi Leungkong, > Can I use VBA to update Access table or Query from Excel? Of course, using ADO or DAO. See: http://www.erlandsendata.no/english/index.php?d=envbadacexportado Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Hi Jan, Thanks. I think ADO is what I want. But I am not only want to export from excel to access. I want to edit some data in access table. For example, Access has a table "ProductList" I want to use Excel to call the product by "Pro...

VBA and Scheduled task in Outlook
Hi, how can I: a) pull the query from somewhere and send it by e-mail automatically. E.g. I want to take a temperature table from http://www.wunderground.com/history/airport/EHAM/2009/7/10/DailyHistory.html?req_city=NA&req_state=NA&req_statename=NA where 2009/7/10 is the date of yesterday put it into an e-mail and send it automatically each day at 8 o'clock? b) if this is not possible, how can I send an e-mail with the attachment c:\Documents\query20090710.xls each day at 8 o'clock? my boss wants this automatized, I know how to write the VBA code for Excel, but don'...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Column width and pasting sections in excel? Formatting questions
I am trying to make a spreadsheet in which I need to have varied column widths in different sections, one under another. (they don't need to relate directly, and no major equations going on) for example: xxxxxxxxx|xxxxxxxxxx xxxxxxxxx|xxxxxxxxxx xxxxxxxxx|xxxxxxxxxx xxxxxxx|xxxxx|xxxxxx xxxxxxx|xxxxx|xxxxxx How do I go about splitting the sheet or whatever I need to do so I can manipulate columns differently based on the row I'm in? Thanks! Rich Column widths apply to the whole column. You may be able to use merged cells to give the appearance that you want, but I try to stay...

How do I link one person to another in the same table?
I have a table of Contacts. Some of my contacts know other contacts, and I would like to indicate that in their record. However, for Data Type, I cannot create a drop down list of contacts from within the table to choose from. Any idea how to to this? Or, if this is not possible, can you suggest another way to indicate "who knows whom"? Your contact table ought to look like: TblContact ContactID FirstName LastName etc Then you need am acquaintenances table: TblAcquaintenance AcquaintenanceID ContactID (identifies a Contact) AcquaintedContactID (Ident...

Restoring Exchange Store files to a new server
We had a major system crash this week but I was able to grab the entire exchsrv directory from the server. What files do I need to copy to the new server to get all of my exchange mail/public folders/etc loaded on the new server? Thanks, CJ What version of Exchange? -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Curious Joe" <joebob.johnson@gmail.com> wrote in message news:1160176822.798021.310710@m73g2000cwd.googlegroups.com... > We had a major system crash this week but I was able to grab the entire > exchsrv director...

vba select field
Hi, having a button on a form, is it possible to select only some fields (all from the same table) of the current record instead of all fields? I've tried this code: RunCommand acCmdSelectRecord RunCommand acCmdCopy Forse dipende dei dati nel record. but it selects ALL fields in the form. Thank you. Remigio hi, On 10.04.2010 18:01, remigio wrote: > having a button on a form, is it possible to select only some fields > (all from the same table) of the current record instead of all fields? > I've tried this code: > > RunCommand acCmdSelectRecord &...

get change operation on coCustomLinks cannot find table
Hello, I have this client that has deployed their server in Singapore but has a branch in Thailand. Since Thailand also needs to use Great Plains, they have installed a GP 8.0 application in a workstation in Thailand and connects to the database deployed in Singapore. But when the staff in thailand logs onto her workstation, she gets the error "get/change first operation on coCustomLinks cannot find table" then all suceeding document processing (i.e. GL entries), go haywire - the next journal entry number doesn't generate. They went through the same installation procedures ...

Excel VBA/Macro programming for beginners
Hi all, I know a bit VB 6.0 programming. Will it help me to write exce vba/macro(same thing?).can anybody help me by providing some fre websites where i can have informations/codes for VBA/macro. Please tell me how could i make a *command Button * in a cell in th excel sheet -- sdebu_200 ----------------------------------------------------------------------- sdebu_2000's Profile: http://www.officehelp.in/member.php?userid=430 View this thread: http://www.officehelp.in/showthread.php?t=118812 Posted from - http://www.officehelp.i Try David McRitchie's "getting started with mac...

Dates in Excel #8
Why does Excel assume that when you type December 2004 that you want the numeric equivalent and not a text entry? Is this just another illustration of Microsoft knowing what we need before we know it? Thanks I don't know why MS assumes this but you can precede the entry with an apostrophe and it will be text -- Regards, Peo Sjoblom "FJB" <FJB0623@aol.com> wrote in message news:1109089977.316088.311020@g14g2000cwa.googlegroups.com... > Why does Excel assume that when you type December 2004 that you want > the numeric equivalent and not a text entry? Is this jus...

Turn pc speaker on/off with vba code
Is this possible? Thanks I found something here on how to do it withVBScript. You can probably modify it to work in VBA http://www.pcreview.co.uk/forums/thread-1468591.php -- HTH, Barb Reinhardt "David" wrote: > Is this possible? > Thanks Thanks Barb I'll have a go (I need to improve my web searching skills) "Barb Reinhardt" wrote: > I found something here on how to do it withVBScript. You can probably modify > it to work in VBA > > http://www.pcreview.co.uk/forums/thread-1468591.php > -- > HTH, > ...

Excel 2000... Missing row and column designators. #3
...

I need help with a table formula/mass editting
I have a spreadsheet (.csv) with 1600 products/rows and several columns. One of the columns that has the product descriptions also contains 1600 rows. In each row under that 'description' column is a formula/code to input a chart/table with product details to our web site in a chart form. I have many new products that I need to place on the web site with this layout. I need to know how to optimize that particular column to add specific details to the chart formula for the 1600 products. For instance, I need the chart to be the same on each product but with different ...

Loading a Form from VBA
Hi all, I'd like a module to open and load a form in my Access 2003 database... How can I do this? Use the OpenForm method. Look in the Help file for details. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Mike" <michael.leon.schwartz@gmail.com> wrote in message news:1184770076.109526.207900@o11g2000prd.googlegroups.com... > Hi all, I'd like a module to open and load a form in my Access 2003 > database... How can I do this? > On Jul 18, 10:58 am, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com&g...