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 befo 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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message ...
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" wrote in message
...
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" wrote in message
...
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