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 said:
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
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 --
:
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)
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