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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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" 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

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

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]

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

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]