From: DennisB on
Thank you. I got the code to work for deleting the records. however, I have
a 16 column and 5,000 record table in Excel that I want to append to an
existing table. I have a range variable for the whole table and I want to
append the range to the SQL table. The SQL table has the same field names
and datatypes.

Is there a quick way to do this or do I have to loop through each range
value (16 per row) and then loop through the rows to update my SQL table?





"ryguy7272" wrote:

> I know I just posted here; not seeing it now though so I'll try once more.
> Sub Rectangle1_Click()
> 'TRUSTED CONNECTION
> On Error GoTo errH
>
> Dim con As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim strPath As String
> Dim intImportRow As Integer
> Dim strFirstName, strLastName As String
>
> Dim server, username, password, table, database As String
>
>
> With Sheets("Sheet1")
>
> server = .TextBox1.Text
> table = .TextBox4.Text
> database = .TextBox5.Text
>
>
> If con.State <> 1 Then
>
> con.Open "Provider=SQLOLEDB;Data Source=" & server &
> ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
> 'con.Open
>
> End If
> 'this is the TRUSTED connection string
>
> Set rs.ActiveConnection = con
>
> 'delete all records first if checkbox checked
> If .CheckBox1 Then
> con.Execute "delete from tbl_demo"
> End If
>
> 'set first row with records to import
> 'you could also just loop thru a range if you want.
> intImportRow = 10
>
> Do Until .Cells(intImportRow, 1) = ""
> strFirstName = .Cells(intImportRow, 1)
> strLastName = .Cells(intImportRow, 2)
>
> 'insert row into database
> con.Execute "insert into tbl_demo (firstname, lastname)
> values ('" & strFirstName & "', '" & strLastName & "')"
>
> intImportRow = intImportRow + 1
> Loop
>
> MsgBox "Done importing", vbInformation
>
> con.Close
> Set con = Nothing
>
> End With
>
> Exit Sub
>
> errH:
> MsgBox Err.Description
> End Sub
>
>
> Notice! Inputs come from 5 TextBoxes (I'm only using 3 of the 5). There
> are many ways to do this. Inputs can come from cells, or be hardcoded,
> whatever you prefer.
>
> Ryan--
>
> --
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "AB" wrote:
>
> > You can try reading the data in excel and pushing the data into the
> > server either as APPEND queries (sql statements) (via
> > connection.execute ...) or as ADODB.Recordset.ADDNew. Try those out
> > and see if you gain any speed advantage. I would not be that surprised
> > if Server Management Studios native solution is faster than any 'home
> > made' but give it a shot - who knows.
> > I'm always pushing the data via the two above methods (append query or
> > recordset.addnew) so I wouldn't have a clue how it compares to the
> > wizard.
> >
> >
> > On May 1, 4:22 pm, DennisB <Denn...(a)discussions.microsoft.com> wrote:
> > > I have an ADO connection to SQL Server 2005 and I use SQL Server Management
> > > Studio's import wizard to import Excel sheets to tables. This is very time
> > > consuming and I was wondering what would be the best way to import the files
> > > to SQL using VBA from Excel. I currently call various stored procedures from
> > > VBA.
> > >
> > > Any code snippets or help?
> > >
> > > DennisB
> >
> > .
> >
From: DennisB on
ryguy7272,

I used your code got it to work. However, now I'm getting an error
everywhere in my application that I have never seen before.

"Code execution has been interrupted"

How can I get rid of this. I have closed my file and the sql server, and
reopened the excel file and started to run my app. Now I'm getting this
error constantly.

what can I do?

Dennis

"DennisB" wrote:

> Thank you. I got the code to work for deleting the records. however, I have
> a 16 column and 5,000 record table in Excel that I want to append to an
> existing table. I have a range variable for the whole table and I want to
> append the range to the SQL table. The SQL table has the same field names
> and datatypes.
>
> Is there a quick way to do this or do I have to loop through each range
> value (16 per row) and then loop through the rows to update my SQL table?
>
>
>
>
>
> "ryguy7272" wrote:
>
> > I know I just posted here; not seeing it now though so I'll try once more.
> > Sub Rectangle1_Click()
> > 'TRUSTED CONNECTION
> > On Error GoTo errH
> >
> > Dim con As New ADODB.Connection
> > Dim rs As New ADODB.Recordset
> > Dim strPath As String
> > Dim intImportRow As Integer
> > Dim strFirstName, strLastName As String
> >
> > Dim server, username, password, table, database As String
> >
> >
> > With Sheets("Sheet1")
> >
> > server = .TextBox1.Text
> > table = .TextBox4.Text
> > database = .TextBox5.Text
> >
> >
> > If con.State <> 1 Then
> >
> > con.Open "Provider=SQLOLEDB;Data Source=" & server &
> > ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
> > 'con.Open
> >
> > End If
> > 'this is the TRUSTED connection string
> >
> > Set rs.ActiveConnection = con
> >
> > 'delete all records first if checkbox checked
> > If .CheckBox1 Then
> > con.Execute "delete from tbl_demo"
> > End If
> >
> > 'set first row with records to import
> > 'you could also just loop thru a range if you want.
> > intImportRow = 10
> >
> > Do Until .Cells(intImportRow, 1) = ""
> > strFirstName = .Cells(intImportRow, 1)
> > strLastName = .Cells(intImportRow, 2)
> >
> > 'insert row into database
> > con.Execute "insert into tbl_demo (firstname, lastname)
> > values ('" & strFirstName & "', '" & strLastName & "')"
> >
> > intImportRow = intImportRow + 1
> > Loop
> >
> > MsgBox "Done importing", vbInformation
> >
> > con.Close
> > Set con = Nothing
> >
> > End With
> >
> > Exit Sub
> >
> > errH:
> > MsgBox Err.Description
> > End Sub
> >
> >
> > Notice! Inputs come from 5 TextBoxes (I'm only using 3 of the 5). There
> > are many ways to do this. Inputs can come from cells, or be hardcoded,
> > whatever you prefer.
> >
> > Ryan--
> >
> > --
> > Ryan---
> > If this information was helpful, please indicate this by clicking ''Yes''.
> >
> >
> > "AB" wrote:
> >
> > > You can try reading the data in excel and pushing the data into the
> > > server either as APPEND queries (sql statements) (via
> > > connection.execute ...) or as ADODB.Recordset.ADDNew. Try those out
> > > and see if you gain any speed advantage. I would not be that surprised
> > > if Server Management Studios native solution is faster than any 'home
> > > made' but give it a shot - who knows.
> > > I'm always pushing the data via the two above methods (append query or
> > > recordset.addnew) so I wouldn't have a clue how it compares to the
> > > wizard.
> > >
> > >
> > > On May 1, 4:22 pm, DennisB <Denn...(a)discussions.microsoft.com> wrote:
> > > > I have an ADO connection to SQL Server 2005 and I use SQL Server Management
> > > > Studio's import wizard to import Excel sheets to tables. This is very time
> > > > consuming and I was wondering what would be the best way to import the files
> > > > to SQL using VBA from Excel. I currently call various stored procedures from
> > > > VBA.
> > > >
> > > > Any code snippets or help?
> > > >
> > > > DennisB
> > >
> > > .
> > >
From: ryguy7272 on
Ok, after a lot of independent research, and after speaking with a friend of
mine, who knows this stuff like no one's business, I think I have to conclude
that this can't be done b/w Excel and SQL Server. I think that it's easy to
concatenate a string, but when you pass that string to a SQL Server table,
SQL Server can't parse that string into the appropriate fields, so it goes
back to it's original form. There seems to be a way to do this using C# and
SQL Server, but I don't know for sure how to do that, and that topic is
getting pretty far way from the original topic.

If anyone can prove me wrong, I'd love to see a solution.

Finally, I'm not sure about the error. I've run this code many times and
never had a single problem.

Ryan---



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"DennisB" wrote:

> ryguy7272,
>
> I used your code got it to work. However, now I'm getting an error
> everywhere in my application that I have never seen before.
>
> "Code execution has been interrupted"
>
> How can I get rid of this. I have closed my file and the sql server, and
> reopened the excel file and started to run my app. Now I'm getting this
> error constantly.
>
> what can I do?
>
> Dennis
>
> "DennisB" wrote:
>
> > Thank you. I got the code to work for deleting the records. however, I have
> > a 16 column and 5,000 record table in Excel that I want to append to an
> > existing table. I have a range variable for the whole table and I want to
> > append the range to the SQL table. The SQL table has the same field names
> > and datatypes.
> >
> > Is there a quick way to do this or do I have to loop through each range
> > value (16 per row) and then loop through the rows to update my SQL table?
> >
> >
> >
> >
> >
> > "ryguy7272" wrote:
> >
> > > I know I just posted here; not seeing it now though so I'll try once more.
> > > Sub Rectangle1_Click()
> > > 'TRUSTED CONNECTION
> > > On Error GoTo errH
> > >
> > > Dim con As New ADODB.Connection
> > > Dim rs As New ADODB.Recordset
> > > Dim strPath As String
> > > Dim intImportRow As Integer
> > > Dim strFirstName, strLastName As String
> > >
> > > Dim server, username, password, table, database As String
> > >
> > >
> > > With Sheets("Sheet1")
> > >
> > > server = .TextBox1.Text
> > > table = .TextBox4.Text
> > > database = .TextBox5.Text
> > >
> > >
> > > If con.State <> 1 Then
> > >
> > > con.Open "Provider=SQLOLEDB;Data Source=" & server &
> > > ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
> > > 'con.Open
> > >
> > > End If
> > > 'this is the TRUSTED connection string
> > >
> > > Set rs.ActiveConnection = con
> > >
> > > 'delete all records first if checkbox checked
> > > If .CheckBox1 Then
> > > con.Execute "delete from tbl_demo"
> > > End If
> > >
> > > 'set first row with records to import
> > > 'you could also just loop thru a range if you want.
> > > intImportRow = 10
> > >
> > > Do Until .Cells(intImportRow, 1) = ""
> > > strFirstName = .Cells(intImportRow, 1)
> > > strLastName = .Cells(intImportRow, 2)
> > >
> > > 'insert row into database
> > > con.Execute "insert into tbl_demo (firstname, lastname)
> > > values ('" & strFirstName & "', '" & strLastName & "')"
> > >
> > > intImportRow = intImportRow + 1
> > > Loop
> > >
> > > MsgBox "Done importing", vbInformation
> > >
> > > con.Close
> > > Set con = Nothing
> > >
> > > End With
> > >
> > > Exit Sub
> > >
> > > errH:
> > > MsgBox Err.Description
> > > End Sub
> > >
> > >
> > > Notice! Inputs come from 5 TextBoxes (I'm only using 3 of the 5). There
> > > are many ways to do this. Inputs can come from cells, or be hardcoded,
> > > whatever you prefer.
> > >
> > > Ryan--
> > >
> > > --
> > > Ryan---
> > > If this information was helpful, please indicate this by clicking ''Yes''.
> > >
> > >
> > > "AB" wrote:
> > >
> > > > You can try reading the data in excel and pushing the data into the
> > > > server either as APPEND queries (sql statements) (via
> > > > connection.execute ...) or as ADODB.Recordset.ADDNew. Try those out
> > > > and see if you gain any speed advantage. I would not be that surprised
> > > > if Server Management Studios native solution is faster than any 'home
> > > > made' but give it a shot - who knows.
> > > > I'm always pushing the data via the two above methods (append query or
> > > > recordset.addnew) so I wouldn't have a clue how it compares to the
> > > > wizard.
> > > >
> > > >
> > > > On May 1, 4:22 pm, DennisB <Denn...(a)discussions.microsoft.com> wrote:
> > > > > I have an ADO connection to SQL Server 2005 and I use SQL Server Management
> > > > > Studio's import wizard to import Excel sheets to tables. This is very time
> > > > > consuming and I was wondering what would be the best way to import the files
> > > > > to SQL using VBA from Excel. I currently call various stored procedures from
> > > > > VBA.
> > > > >
> > > > > Any code snippets or help?
> > > > >
> > > > > DennisB
> > > >
> > > > .
> > > >
First  |  Prev  | 
Pages: 1 2
Prev: When recording
Next: Help in VBA code