Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transmitting data from a server spreadsheet to a client spreadsheet | Excel Programming | |||
html form to update excel file on server. | Excel Programming | |||
Automatically update links when server names change | Excel Worksheet Functions | |||
SQL Server UPDATE Database from Excel Table | Links and Linking in Excel | |||
Worksheet gets update from DDE server | Excel Programming |