Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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
share|improve this question
    
so you need to use an ALTER TABLE query to add the new columns to the table, and execute it with the EXCCUTE method of the ADODB.Connection - is that what you want? –  Philip May 27 '13 at 20:57
    
Can i use the alter table also with fields who are allready exists ? or do i need to check first for the missing fields and use the alter table to add them ? –  alfl May 28 '13 at 6:49
    
@alft: yes, you can...see MSDN: T-SQL ALTER TABLE Statment and TechOnTheNET - SQL: ALTER TABLE Statement –  Philip May 28 '13 at 8:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.