I need some help with an sql statement. I have an Excel sheet with variable fields, e.g.
sheet 1: customer name, address, phone 1
sheet 2: customer name, address, phone 1, phone 2
sheet 3: customer name, address, address 2, phone 1
This data is needed to enter in an SQL database. I have the database created, and I am able to fill the data, but I don't know if it is possible to create an update statement in SQL to check for the missing fields and add them to the database if this is not available.
The database has multiple customers all running under their own ID, so customer1 has id 1 and all fields start with the customer id.
So the database has an layout like customerid, name, address, phone 1.
PS. the database used is an Access database.
I am not an SQL expert and couldn't find anything on the net which could help me, but maybe one of you geniuses can help me??
Code used to update the database:
Function ExecuteQuery(sQuery As String)
Dim Cn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim Records As Integer
Dim strMDB As String
Dim ReturnValue() As String
Dim sTemp As String
Dim SplitQuery As Variant
Dim recs As Integer
On Error GoTo ExitHere
strMDB = ThisWorkbook.Path & "\klantserverinfo.accdb"
Set Rs = CreateObject("ADODB.RecordSet")
Set Cn = CreateObject("ADODB.Connection")
With Cn
.CursorLocation = adUseClient
.ConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & strMDB
.Open
End With
SplitQuery = Split(sQuery, " ")
Select Case LCase(SplitQuery(0))
Case "update"
Cn.Execute sQuery, recs
ReturnValue(0) = recs
Case Else
' execute query
Rs.Open sQuery, Cn, adOpenDynamic, adLockOptimistic
Debug.Print Rs.RecordCount
ReDim Preserve ReturnValue(Rs.RecordCount)
For Records = 0 To Rs.RecordCount
ReturnValue(Records) = Rs(Records).Value
Next
End Select
ExitHere:
' if error occured then return function with text "<sql error>"
Debug.Print "Err: " & Err.Number & ", Err desc: " & Err.Description
If Err.Number <> 0 Then
ReDim Preserve ReturnValue(0)
ReturnValue(0) = "<sql error>"
End If
ExecuteQuery = ReturnValue
' Close connections
Debug.Print Rs.State
Debug.Print Cn.State
If Rs.State = 1 Then Rs.Close
If Cn.State = 1 Then Cn.Close
Set Rs = Nothing
Set Cn = Nothing
On Error GoTo 0
Err.Clear
End Function
ALTER TABLE
query to add the new columns to the table, and execute it with theEXCCUTE
method of the ADODB.Connection - is that what you want? – Philip May 27 '13 at 20:57