How to use VBA to update a SQL Server Table from a Spreadsheet

I need to use VBA code to update a table in a SQL Server database. I have
not done anything like this before. Can someone point me in the right
direction?

Thanks,

Keith

It's not clear from your question which bits of "anything like this"
you haven't done before: assuming you know something about
writing VBA and SQL statement, you need to use the ADODB
objects - you might find this MS article useful as a start:

http://support.microsoft.com/default...b;EN-US;257819

Andrew


keithb wrote:
> I need to use VBA code to update a table in a SQL Server database. I have
> not done anything like this before. Can someone point me in the right
> direction?
>
> Thanks,
>
> Keith

Here are three routines that work with Access, you will need a different
connection string for SQL Server, probably something like

oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"


Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub

Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing
End Sub

Sub UpdateData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "c:\bob.mdb"

sSQL = "SELECT * From Contacts"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If oRS.EOF Then
MsgBox "No records returned.", vbCritical
Else
sSQL = "UPDATE Contacts " & _
" SET Phone = 'None' " & _
"WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
oRS.ActiveConnection.Execute sSQL

sSQL = "SELECT * From Contacts"
oRS.ActiveConnection.Execute sSQL
ary = oRS.getrows
MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
End If


oRS.Close
Set oRS = Nothing
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"keithb" <[email protected]> wrote in message
news:%[email protected]...
> I need to use VBA code to update a table in a SQL Server database. I have
> not done anything like this before. Can someone point me in the right
> direction?
>
> Thanks,
>
> Keith
>
>

This is a helpful post for my situation -- is there any way I can push data
directly into the MS SQL database w/o having to push one line at a time? In
the example you showed it says "

sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
" VALUES ('Bob','Phillips','01202 345678','me')"

If I wanted to add a large amount of data directly from Excel (as opposed to
the one record shown above) is there a good way to do that from VBA? Maybe
where I have VBA export the info that I want to update or insert out to a csv
file and then use the vba to pick up the csv file and push it into the MS SQL
database?

Thanks --



"Bob Phillips" wrote:

> Here are three routines that work with Access, you will need a different
> connection string for SQL Server, probably something like
>
> oConn.Open "Provider=sqloledb;" & _
> "Data Source=myServerName;" & _
> "Initial Catalog=myDatabaseName;" & _
> "User Id=myUsername;" & _
> "Password=myPassword"
>
>
> Sub AddData()
> Dim oConn As Object
> Dim oRS As Object
> Dim sSQL As String
>
> Set oConn = CreateObject("ADODB.Connection")
> oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & "c:\bob.mdb"
>
> sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
> " VALUES ('Bob','Phillips','01202 345678','me')"
> oConn.Execute sSQL
>
> oConn.Close
> Set oConn = Nothing
> End Sub
>
> Sub GetData()
> Const adOpenForwardOnly As Long = 0
> Const adLockReadOnly As Long = 1
> Const adCmdText As Long = 1
> Dim oRS As Object
> Dim sConnect As String
> Dim sSQL As String
> Dim ary
>
> sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & "c:\bob.mdb"
>
> sSQL = "SELECT * From Contacts"
> Set oRS = CreateObject("ADODB.Recordset")
> oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> adLockReadOnly, adCmdText
>
> ' Check to make sure we received data.
> If Not oRS.EOF Then
> ary = oRS.getrows
> MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> Else
> MsgBox "No records returned.", vbCritical
> End If
>
> oRS.Close
> Set oRS = Nothing
> End Sub
>
> Sub UpdateData()
> Const adOpenForwardOnly As Long = 0
> Const adLockReadOnly As Long = 1
> Const adCmdText As Long = 1
> Dim oConn As Object
> Dim oRS As Object
> Dim sConnect As String
> Dim sSQL As String
> Dim ary
>
> sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & "c:\bob.mdb"
>
> sSQL = "SELECT * From Contacts"
> Set oRS = CreateObject("ADODB.Recordset")
> oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> adLockReadOnly, adCmdText
>
> ' Check to make sure we received data.
> If oRS.EOF Then
> MsgBox "No records returned.", vbCritical
> Else
> sSQL = "UPDATE Contacts " & _
> " SET Phone = 'None' " & _
> "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
> oRS.ActiveConnection.Execute sSQL
>
> sSQL = "SELECT * From Contacts"
> oRS.ActiveConnection.Execute sSQL
> ary = oRS.getrows
> MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> End If
>
>
> oRS.Close
> Set oRS = Nothing
> End Sub
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "keithb" <[email protected]> wrote in message
> news:%[email protected]...
> > I need to use VBA code to update a table in a SQL Server database. I have
> > not done anything like this before. Can someone point me in the right
> > direction?
> >
> > Thanks,
> >
> > Keith
> >
> >
>
>
>

Depends on what you mean by "large amount". You could just run the update
SQL in a loop and performance should be pretty good (depending partly on the
performance of your DB).



--
Tim Williams
Palo Alto, CA


"Rubble" <[email protected]> wrote in message
news:[email protected]...
> This is a helpful post for my situation -- is there any way I can push
data
> directly into the MS SQL database w/o having to push one line at a time?
In
> the example you showed it says "
>
> sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
> " VALUES ('Bob','Phillips','01202 345678','me')"
>
> If I wanted to add a large amount of data directly from Excel (as opposed
to
> the one record shown above) is there a good way to do that from VBA?
Maybe
> where I have VBA export the info that I want to update or insert out to a
csv
> file and then use the vba to pick up the csv file and push it into the MS
SQL
> database?
>
> Thanks --
>
>
>
> "Bob Phillips" wrote:
>
> > Here are three routines that work with Access, you will need a different
> > connection string for SQL Server, probably something like
> >
> > oConn.Open "Provider=sqloledb;" & _
> > "Data Source=myServerName;" & _
> > "Initial Catalog=myDatabaseName;" & _
> > "User Id=myUsername;" & _
> > "Password=myPassword"
> >
> >
> > Sub AddData()
> > Dim oConn As Object
> > Dim oRS As Object
> > Dim sSQL As String
> >
> > Set oConn = CreateObject("ADODB.Connection")
> > oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & "c:\bob.mdb"
> >
> > sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " &
_
> > " VALUES ('Bob','Phillips','01202 345678','me')"
> > oConn.Execute sSQL
> >
> > oConn.Close
> > Set oConn = Nothing
> > End Sub
> >
> > Sub GetData()
> > Const adOpenForwardOnly As Long = 0
> > Const adLockReadOnly As Long = 1
> > Const adCmdText As Long = 1
> > Dim oRS As Object
> > Dim sConnect As String
> > Dim sSQL As String
> > Dim ary
> >
> > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & "c:\bob.mdb"
> >
> > sSQL = "SELECT * From Contacts"
> > Set oRS = CreateObject("ADODB.Recordset")
> > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > adLockReadOnly, adCmdText
> >
> > ' Check to make sure we received data.
> > If Not oRS.EOF Then
> > ary = oRS.getrows
> > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > Else
> > MsgBox "No records returned.", vbCritical
> > End If
> >
> > oRS.Close
> > Set oRS = Nothing
> > End Sub
> >
> > Sub UpdateData()
> > Const adOpenForwardOnly As Long = 0
> > Const adLockReadOnly As Long = 1
> > Const adCmdText As Long = 1
> > Dim oConn As Object
> > Dim oRS As Object
> > Dim sConnect As String
> > Dim sSQL As String
> > Dim ary
> >
> > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & "c:\bob.mdb"
> >
> > sSQL = "SELECT * From Contacts"
> > Set oRS = CreateObject("ADODB.Recordset")
> > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > adLockReadOnly, adCmdText
> >
> > ' Check to make sure we received data.
> > If oRS.EOF Then
> > MsgBox "No records returned.", vbCritical
> > Else
> > sSQL = "UPDATE Contacts " & _
> > " SET Phone = 'None' " & _
> > "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
> > oRS.ActiveConnection.Execute sSQL
> >
> > sSQL = "SELECT * From Contacts"
> > oRS.ActiveConnection.Execute sSQL
> > ary = oRS.getrows
> > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > End If
> >
> >
> > oRS.Close
> > Set oRS = Nothing
> > End Sub
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "keithb" <[email protected]> wrote in message
> > news:%[email protected]...
> > > I need to use VBA code to update a table in a SQL Server database. I
have
> > > not done anything like this before. Can someone point me in the right
> > > direction?
> > >
> > > Thanks,
> > >
> > > Keith
> > >
> > >
> >
> >
> >

I did the loop in vba -- it seems to work pretty well. When I am pushing in
data I am typiclly only pushing in about 100-200 records so the loop works
pretty well with that. In some instances I am pushing in about 160,000
records (several tabs of excel data).

I saw in a MSDN article a line of code that would be something like the
following ...

jSQL = "SELECT * FROM [tblType$]"

It looks like this line of code should pick up all of the data from a tab
named "tblTypes" in the current workbook. I have tried messing around with
this code, but cannot seem to get it working -- I am thinking I am supposed
to tell vba that I am actually looking in the current workbook or something,
but don't know for sure how to do that. I can use some examples they have in
the code where you put in a path and filename, but it seems like there is a
more simple way to do it. I guess I could call up the path and filename of
the current workbook in vba and then use that to identify what I am trying to
get -- but it just seems I am going somewhere with that where I don't need to
be going --

Any ideas?

"Tim Williams" wrote:

> Depends on what you mean by "large amount". You could just run the update
> SQL in a loop and performance should be pretty good (depending partly on the
> performance of your DB).
>
>
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Rubble" <[email protected]> wrote in message
> news:[email protected]...
> > This is a helpful post for my situation -- is there any way I can push
> data
> > directly into the MS SQL database w/o having to push one line at a time?
> In
> > the example you showed it says "
> >
> > sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
> > " VALUES ('Bob','Phillips','01202 345678','me')"
> >
> > If I wanted to add a large amount of data directly from Excel (as opposed
> to
> > the one record shown above) is there a good way to do that from VBA?
> Maybe
> > where I have VBA export the info that I want to update or insert out to a
> csv
> > file and then use the vba to pick up the csv file and push it into the MS
> SQL
> > database?
> >
> > Thanks --
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> > > Here are three routines that work with Access, you will need a different
> > > connection string for SQL Server, probably something like
> > >
> > > oConn.Open "Provider=sqloledb;" & _
> > > "Data Source=myServerName;" & _
> > > "Initial Catalog=myDatabaseName;" & _
> > > "User Id=myUsername;" & _
> > > "Password=myPassword"
> > >
> > >
> > > Sub AddData()
> > > Dim oConn As Object
> > > Dim oRS As Object
> > > Dim sSQL As String
> > >
> > > Set oConn = CreateObject("ADODB.Connection")
> > > oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > "Data Source=" & "c:\bob.mdb"
> > >
> > > sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " &
> _
> > > " VALUES ('Bob','Phillips','01202 345678','me')"
> > > oConn.Execute sSQL
> > >
> > > oConn.Close
> > > Set oConn = Nothing
> > > End Sub
> > >
> > > Sub GetData()
> > > Const adOpenForwardOnly As Long = 0
> > > Const adLockReadOnly As Long = 1
> > > Const adCmdText As Long = 1
> > > Dim oRS As Object
> > > Dim sConnect As String
> > > Dim sSQL As String
> > > Dim ary
> > >
> > > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > "Data Source=" & "c:\bob.mdb"
> > >
> > > sSQL = "SELECT * From Contacts"
> > > Set oRS = CreateObject("ADODB.Recordset")
> > > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > > adLockReadOnly, adCmdText
> > >
> > > ' Check to make sure we received data.
> > > If Not oRS.EOF Then
> > > ary = oRS.getrows
> > > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > > Else
> > > MsgBox "No records returned.", vbCritical
> > > End If
> > >
> > > oRS.Close
> > > Set oRS = Nothing
> > > End Sub
> > >
> > > Sub UpdateData()
> > > Const adOpenForwardOnly As Long = 0
> > > Const adLockReadOnly As Long = 1
> > > Const adCmdText As Long = 1
> > > Dim oConn As Object
> > > Dim oRS As Object
> > > Dim sConnect As String
> > > Dim sSQL As String
> > > Dim ary
> > >
> > > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > "Data Source=" & "c:\bob.mdb"
> > >
> > > sSQL = "SELECT * From Contacts"
> > > Set oRS = CreateObject("ADODB.Recordset")
> > > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > > adLockReadOnly, adCmdText
> > >
> > > ' Check to make sure we received data.
> > > If oRS.EOF Then
> > > MsgBox "No records returned.", vbCritical
> > > Else
> > > sSQL = "UPDATE Contacts " & _
> > > " SET Phone = 'None' " & _
> > > "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
> > > oRS.ActiveConnection.Execute sSQL
> > >
> > > sSQL = "SELECT * From Contacts"
> > > oRS.ActiveConnection.Execute sSQL
> > > ary = oRS.getrows
> > > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > > End If
> > >
> > >
> > > oRS.Close
> > > Set oRS = Nothing
> > > End Sub
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > (remove nothere from email address if mailing direct)
> > >
> > > "keithb" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > I need to use VBA code to update a table in a SQL Server database. I
> have
> > > > not done anything like this before. Can someone point me in the right
> > > > direction?
> > > >
> > > > Thanks,
> > > >
> > > > Keith
> > > >
> > > >
> > >
> > >
> > >
>
>
>

Picking up the data from the sheet is probably going to be one of the faster parts of the whole process....

If you still want to look into this then you might find it easier to pick the data off the sheet just by reading it into an array

Dim vArr
vArr = thisworkbook.Sheets("sheetname").range("A1:D50000").value

Then iterate through vArr (now a 2-D array).

If you want to improve speed at the database side then you might also look at batching your inserts using multiple insert statements
separated by ";" in a single "Execute" call.

Eg:
sSQL = "insert into.....; insert into......; insert into......."
oConn.Execute sSQL


I think that's supported. And make sure you're only opening your connection once!

--
Tim Williams
Palo Alto, CA


"Rubble" <[email protected]> wrote in message news:[email protected]...
> I did the loop in vba -- it seems to work pretty well. When I am pushing in
> data I am typiclly only pushing in about 100-200 records so the loop works
> pretty well with that. In some instances I am pushing in about 160,000
> records (several tabs of excel data).
>
> I saw in a MSDN article a line of code that would be something like the
> following ...
>
> jSQL = "SELECT * FROM [tblType$]"
>
> It looks like this line of code should pick up all of the data from a tab
> named "tblTypes" in the current workbook. I have tried messing around with
> this code, but cannot seem to get it working -- I am thinking I am supposed
> to tell vba that I am actually looking in the current workbook or something,
> but don't know for sure how to do that. I can use some examples they have in
> the code where you put in a path and filename, but it seems like there is a
> more simple way to do it. I guess I could call up the path and filename of
> the current workbook in vba and then use that to identify what I am trying to
> get -- but it just seems I am going somewhere with that where I don't need to
> be going --
>
> Any ideas?
>
> "Tim Williams" wrote:
>
> > Depends on what you mean by "large amount". You could just run the update
> > SQL in a loop and performance should be pretty good (depending partly on the
> > performance of your DB).
> >
> >
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "Rubble" <[email protected]> wrote in message
> > news:[email protected]...
> > > This is a helpful post for my situation -- is there any way I can push
> > data
> > > directly into the MS SQL database w/o having to push one line at a time?
> > In
> > > the example you showed it says "
> > >
> > > sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
> > > " VALUES ('Bob','Phillips','01202 345678','me')"
> > >
> > > If I wanted to add a large amount of data directly from Excel (as opposed
> > to
> > > the one record shown above) is there a good way to do that from VBA?
> > Maybe
> > > where I have VBA export the info that I want to update or insert out to a
> > csv
> > > file and then use the vba to pick up the csv file and push it into the MS
> > SQL
> > > database?
> > >
> > > Thanks --
> > >
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Here are three routines that work with Access, you will need a different
> > > > connection string for SQL Server, probably something like
> > > >
> > > > oConn.Open "Provider=sqloledb;" & _
> > > > "Data Source=myServerName;" & _
> > > > "Initial Catalog=myDatabaseName;" & _
> > > > "User Id=myUsername;" & _
> > > > "Password=myPassword"
> > > >
> > > >
> > > > Sub AddData()
> > > > Dim oConn As Object
> > > > Dim oRS As Object
> > > > Dim sSQL As String
> > > >
> > > > Set oConn = CreateObject("ADODB.Connection")
> > > > oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > > "Data Source=" & "c:\bob.mdb"
> > > >
> > > > sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " &
> > _
> > > > " VALUES ('Bob','Phillips','01202 345678','me')"
> > > > oConn.Execute sSQL
> > > >
> > > > oConn.Close
> > > > Set oConn = Nothing
> > > > End Sub
> > > >
> > > > Sub GetData()
> > > > Const adOpenForwardOnly As Long = 0
> > > > Const adLockReadOnly As Long = 1
> > > > Const adCmdText As Long = 1
> > > > Dim oRS As Object
> > > > Dim sConnect As String
> > > > Dim sSQL As String
> > > > Dim ary
> > > >
> > > > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > > "Data Source=" & "c:\bob.mdb"
> > > >
> > > > sSQL = "SELECT * From Contacts"
> > > > Set oRS = CreateObject("ADODB.Recordset")
> > > > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > > > adLockReadOnly, adCmdText
> > > >
> > > > ' Check to make sure we received data.
> > > > If Not oRS.EOF Then
> > > > ary = oRS.getrows
> > > > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > > > Else
> > > > MsgBox "No records returned.", vbCritical
> > > > End If
> > > >
> > > > oRS.Close
> > > > Set oRS = Nothing
> > > > End Sub
> > > >
> > > > Sub UpdateData()
> > > > Const adOpenForwardOnly As Long = 0
> > > > Const adLockReadOnly As Long = 1
> > > > Const adCmdText As Long = 1
> > > > Dim oConn As Object
> > > > Dim oRS As Object
> > > > Dim sConnect As String
> > > > Dim sSQL As String
> > > > Dim ary
> > > >
> > > > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > > "Data Source=" & "c:\bob.mdb"
> > > >
> > > > sSQL = "SELECT * From Contacts"
> > > > Set oRS = CreateObject("ADODB.Recordset")
> > > > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > > > adLockReadOnly, adCmdText
> > > >
> > > > ' Check to make sure we received data.
> > > > If oRS.EOF Then
> > > > MsgBox "No records returned.", vbCritical
> > > > Else
> > > > sSQL = "UPDATE Contacts " & _
> > > > " SET Phone = 'None' " & _
> > > > "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
> > > > oRS.ActiveConnection.Execute sSQL
> > > >
> > > > sSQL = "SELECT * From Contacts"
> > > > oRS.ActiveConnection.Execute sSQL
> > > > ary = oRS.getrows
> > > > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > > > End If
> > > >
> > > >
> > > > oRS.Close
> > > > Set oRS = Nothing
> > > > End Sub
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from email address if mailing direct)
> > > >
> > > > "keithb" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > I need to use VBA code to update a table in a SQL Server database. I
> > have
> > > > > not done anything like this before. Can someone point me in the right
> > > > > direction?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > Keith
> > > > >
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

That makes sense -- I wasn't thinking of performance on the database side so
that is a good point. I'll just pick up the data in smaller groups and then
send it in to the sql server.

THANK YOU !!! this is extremely helpful to my situation.

One more quick question. When writing data to a SQL server I have taken your
advice and pushed data into the database in smaller chunks. The question I
have now is that when I was writing some data to a database this morning I
was trying to write some pretty large decimal numbers to some fields. When I
looked at the data in the sql database it all showed up as "0's". My field
is defined as a decimal with precision to 18. I can't seem to figure out why
this isn't working at the moment. Any ideas? Your previous post has helped
me immensely!!

Thank you --

"Tim Williams" wrote:

> Picking up the data from the sheet is probably going to be one of the faster parts of the whole process....
>
> If you still want to look into this then you might find it easier to pick the data off the sheet just by reading it into an array
>
> Dim vArr
> vArr = thisworkbook.Sheets("sheetname").range("A1:D50000").value
>
> Then iterate through vArr (now a 2-D array).
>
> If you want to improve speed at the database side then you might also look at batching your inserts using multiple insert statements
> separated by ";" in a single "Execute" call.
>
> Eg:
> sSQL = "insert into.....; insert into......; insert into......."
> oConn.Execute sSQL
>
>
> I think that's supported. And make sure you're only opening your connection once!
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Rubble" <[email protected]> wrote in message news:[email protected]...
> > I did the loop in vba -- it seems to work pretty well. When I am pushing in
> > data I am typiclly only pushing in about 100-200 records so the loop works
> > pretty well with that. In some instances I am pushing in about 160,000
> > records (several tabs of excel data).
> >
> > I saw in a MSDN article a line of code that would be something like the
> > following ...
> >
> > jSQL = "SELECT * FROM [tblType$]"
> >
> > It looks like this line of code should pick up all of the data from a tab
> > named "tblTypes" in the current workbook. I have tried messing around with
> > this code, but cannot seem to get it working -- I am thinking I am supposed
> > to tell vba that I am actually looking in the current workbook or something,
> > but don't know for sure how to do that. I can use some examples they have in
> > the code where you put in a path and filename, but it seems like there is a
> > more simple way to do it. I guess I could call up the path and filename of
> > the current workbook in vba and then use that to identify what I am trying to
> > get -- but it just seems I am going somewhere with that where I don't need to
> > be going --
> >
> > Any ideas?
> >
> > "Tim Williams" wrote:
> >
> > > Depends on what you mean by "large amount". You could just run the update
> > > SQL in a loop and performance should be pretty good (depending partly on the
> > > performance of your DB).
> > >
> > >
> > >
> > > --
> > > Tim Williams
> > > Palo Alto, CA
> > >
> > >
> > > "Rubble" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > This is a helpful post for my situation -- is there any way I can push
> > > data
> > > > directly into the MS SQL database w/o having to push one line at a time?
> > > In
> > > > the example you showed it says "
> > > >
> > > > sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " & _
> > > > " VALUES ('Bob','Phillips','01202 345678','me')"
> > > >
> > > > If I wanted to add a large amount of data directly from Excel (as opposed
> > > to
> > > > the one record shown above) is there a good way to do that from VBA?
> > > Maybe
> > > > where I have VBA export the info that I want to update or insert out to a
> > > csv
> > > > file and then use the vba to pick up the csv file and push it into the MS
> > > SQL
> > > > database?
> > > >
> > > > Thanks --
> > > >
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Here are three routines that work with Access, you will need a different
> > > > > connection string for SQL Server, probably something like
> > > > >
> > > > > oConn.Open "Provider=sqloledb;" & _
> > > > > "Data Source=myServerName;" & _
> > > > > "Initial Catalog=myDatabaseName;" & _
> > > > > "User Id=myUsername;" & _
> > > > > "Password=myPassword"
> > > > >
> > > > >
> > > > > Sub AddData()
> > > > > Dim oConn As Object
> > > > > Dim oRS As Object
> > > > > Dim sSQL As String
> > > > >
> > > > > Set oConn = CreateObject("ADODB.Connection")
> > > > > oConn.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > > > "Data Source=" & "c:\bob.mdb"
> > > > >
> > > > > sSQL = "INSERT INTO Contacts (FirstName, LastName,Phone, Notes) " &
> > > _
> > > > > " VALUES ('Bob','Phillips','01202 345678','me')"
> > > > > oConn.Execute sSQL
> > > > >
> > > > > oConn.Close
> > > > > Set oConn = Nothing
> > > > > End Sub
> > > > >
> > > > > Sub GetData()
> > > > > Const adOpenForwardOnly As Long = 0
> > > > > Const adLockReadOnly As Long = 1
> > > > > Const adCmdText As Long = 1
> > > > > Dim oRS As Object
> > > > > Dim sConnect As String
> > > > > Dim sSQL As String
> > > > > Dim ary
> > > > >
> > > > > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > > > "Data Source=" & "c:\bob.mdb"
> > > > >
> > > > > sSQL = "SELECT * From Contacts"
> > > > > Set oRS = CreateObject("ADODB.Recordset")
> > > > > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > > > > adLockReadOnly, adCmdText
> > > > >
> > > > > ' Check to make sure we received data.
> > > > > If Not oRS.EOF Then
> > > > > ary = oRS.getrows
> > > > > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > > > > Else
> > > > > MsgBox "No records returned.", vbCritical
> > > > > End If
> > > > >
> > > > > oRS.Close
> > > > > Set oRS = Nothing
> > > > > End Sub
> > > > >
> > > > > Sub UpdateData()
> > > > > Const adOpenForwardOnly As Long = 0
> > > > > Const adLockReadOnly As Long = 1
> > > > > Const adCmdText As Long = 1
> > > > > Dim oConn As Object
> > > > > Dim oRS As Object
> > > > > Dim sConnect As String
> > > > > Dim sSQL As String
> > > > > Dim ary
> > > > >
> > > > > sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > > > > "Data Source=" & "c:\bob.mdb"
> > > > >
> > > > > sSQL = "SELECT * From Contacts"
> > > > > Set oRS = CreateObject("ADODB.Recordset")
> > > > > oRS.Open sSQL, sConnect, adOpenForwardOnly, _
> > > > > adLockReadOnly, adCmdText
> > > > >
> > > > > ' Check to make sure we received data.
> > > > > If oRS.EOF Then
> > > > > MsgBox "No records returned.", vbCritical
> > > > > Else
> > > > > sSQL = "UPDATE Contacts " & _
> > > > > " SET Phone = 'None' " & _
> > > > > "WHERE FirstName = 'Bob' AND LastNAme = 'Phillips'"
> > > > > oRS.ActiveConnection.Execute sSQL
> > > > >
> > > > > sSQL = "SELECT * From Contacts"
> > > > > oRS.ActiveConnection.Execute sSQL
> > > > > ary = oRS.getrows
> > > > > MsgBox ary(0, 0) & " " & ary(1, 0) & ", " & ary(2, 0)
> > > > > End If
> > > > >
> > > > >
> > > > > oRS.Close
> > > > > Set oRS = Nothing
> > > > > End Sub
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > (remove nothere from email address if mailing direct)
> > > > >
> > > > > "keithb" <[email protected]> wrote in message
> > > > > news:%[email protected]...
> > > > > > I need to use VBA code to update a table in a SQL Server database. I
> > > have
> > > > > > not done anything like this before. Can someone point me in the right
> > > > > > direction?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > Keith
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Hi Keith,

Rubble wrote:
> The question I have now is that when I was writing some data
> to a database this morning I was trying to write some pretty large
> decimal numbers to some fields. When I looked at the data in the sql
> database it all showed up as "0's". My field is defined as a decimal
> with precision to 18. I can't seem to figure out why this isn't
> working at the moment. Any ideas?

Precision and Scale are terms that can be somewhat confusing IMO. Precision
of 18 means that you can have 18 digits in your number. But Scale is what
determines the number of decimal places. For example, if you set Precision
to 18 and Scale to 10, you can have 18 digits - 8 to the left of the decimal
point and 10 to the right. Does that help?

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

THANK YOU !!! That is evidence that I truly am an idiot. Thanks Again !!

"Jake Marx" wrote:

> Hi Keith,
>
> Rubble wrote:
> > The question I have now is that when I was writing some data
> > to a database this morning I was trying to write some pretty large
> > decimal numbers to some fields. When I looked at the data in the sql
> > database it all showed up as "0's". My field is defined as a decimal
> > with precision to 18. I can't seem to figure out why this isn't
> > working at the moment. Any ideas?
>
> Precision and Scale are terms that can be somewhat confusing IMO. Precision
> of 18 means that you can have 18 digits in your number. But Scale is what
> determines the number of decimal places. For example, if you set Precision
> to 18 and Scale to 10, you can have 18 digits - 8 to the left of the decimal
> point and 10 to the right. Does that help?
>
> --
> Regards,
>
> Jake Marx
> www.longhead.com
>
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>
>