VBA code export to SQL help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greeny129 - 05 Feb 2007 17:38 GMT

Hi Guys,

I would like to update an SQL DB called tblnames- it contains the following
fields : Department;Name;TL;userid.

I would like to update from an excel spreadsheet by entering the relevant
details and then clicking a button.

Is this possible and if so can anyone provide thecoding i would need to use.

Thanks

Reply to this Message

Bob Phillips - 05 Feb 2007 17:50 GMT

Here is some code to update an Access database from Excel

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

You would need a different connect strng, see
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Guys,
>
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

Greeny129 - 06 Feb 2007 07:54 GMT

HI Bob - thanks so much for your response - i'll give it a try today, its
people like you that make the world go around.

Thanks again.

> Here is some code to update an Access database from Excel
>
[quoted text clipped - 51 lines]
> >
> > Thanks

Reply to this Message

Greeny129 - 06 Feb 2007 09:25 GMT

Hi Bob,

Tried the code - seems to kick out at various points, below is the code that
exists however doesn't seem to work, actual feilds are as follows
Department;UserName;TelLogin;LoginID;UserID;TL

Private Sub CommandButton2_Click()
'Inserts a new record into an SQL Table

Dim con As Object
Dim d As String
Dim boolcheck As Boolean
Set con = CreateObject("ADODB.Connection")

Sheets("Sheet2").Select
Selection.Range("A1").Select
With Selection.QueryTable
.BackgroundQuery = False
.Refresh
End With

Sheets("sheet1").Select

boolcheck = False

If Not (Sheets("Sheet2").Range("A2").Value = "") Then
boolcheck = True
Call CommandButton1_Click
Else
' sets Excel as a Jet connection so that SQL recognises it
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name &
";" & _
"Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"

' uses SQL string language to extract the data from Excel into the SQL
table
con.Execute _
"INSERT INTO [ODBC;Driver={SQL Server};" & _
"SERVER=SSQTRNT03CGCFGE;DATABASE=BCMIS;" & _
"UID=BCMISXLS;Pwd=BANKCARDMIS;].TblNames" & _
" select * FROM [Sheet1$];"

' closes the SQL connection

con.Close
Set con = Nothing

End If

End Sub

Is there a way that this could be made to work - the db has exactly same
feilds with validation only on USER ID.

Thanks

Matt

> HI Bob - thanks so much for your response - i'll give it a try today, its
> people like you that make the world go around.
[quoted text clipped - 56 lines]
> > >
> > > Thanks

Reply to this Message

Bob Phillips - 06 Feb 2007 12:55 GMT

Matt,

It is difficult to debug it remotely, but the obvious thing I notice is that
you say you are trying to update an SQL Table (I read this as SQL-Server,
correct?), but are using a connection to the Jet engine to connect to a
workbook. That doesn't look right to me. Did you not see my ending aside in
the previous response

You would need a different connect string, see
http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForSQLServer

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi Bob,
>
[quoted text clipped - 120 lines]
>> > >
>> > > Thanks

Reply to this Message