EXCEL export to SQL query

[Guest]

Hi All,

Hi guys - I'm a bit of a newbie to this programming lark and am struggling -
I am trying to update a SQL database from a form in excel, below is the code
that exists however doesn't seem to work, actual feilds on Spreadsheet and
SQL DB 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=MATT2;Pwd=MATT;].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, if someone could supply the code it
would be greatly appreciated

This is a repost from one i posted yesterday (Thanks for the assistance Bob)
- however it contains more information.

Thanks

Matt

 

[Bob Phillips]

Matt,

As mentioned before, working blind here, but I would have thought your code
should be more like


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
con.Open "Provider=sqloledb;" & _
"Data Source=SSQTRNT03CGCFGE;" & _
"Initial Catalog=BCMIS;" & _
"User Id=MATT2;" & _
"Password=MATT"

sSQL = "INSERT INTO Tablename " & _
" (FirstName, LastName,Phone, Notes) " & _
"VALUES ('Bob','Phillips','01202 345678','me')"

' uses SQL string language to extract the data from Excel into the SQL
Table
con.Execute sSQL

' closes the SQL connection

con.Close
Set con = Nothing

End If

End Sub

You would need to provide the Tablename, and the correct column names, and
where the values come from.

--
---
HTH

Bob

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

Hi All,

Hi guys - I'm a bit of a newbie to this programming lark and am
struggling -
I am trying to update a SQL database from a form in excel, below is the
code
that exists however doesn't seem to work, actual feilds on Spreadsheet and
SQL DB 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=MATT2;Pwd=MATT;].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, if someone could supply the code
it
would be greatly appreciated

This is a repost from one i posted yesterday (Thanks for the assistance
Bob)
- however it contains more information.

Thanks

Matt