create an access quey to do the following

[samipm]

Hi guys

The following code is run when a click button is clicked on a form


Const ExcelFile As String = "Employees.xls"
Dim ExcApp As Object
Dim WrkBk As Object
Dim WrkSht As Object
Dim CurrRow As Integer
Dim GNam As String
Dim LNam As String
Dim SexFld As String
Dim SqlCmd As String

On Error Resume Next

Set ExcApp = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
'Excel wasn't running, start it from code
Set ExcApp = CreateObject("Excel.Application")
End If

On Error GoTo 0

' Opens Excel and makes it Visible
Set WrkBk = ExcApp.Workbooks.Open(CurrentProject.Path & "\" & ExcelFile, ReadOnly:=True)
Set WrkSht = WrkBk.Sheets("Customer")
CurrRow = 2

Do Until IsEmpty(WrkSht.Cells(CurrRow, "B").Value)
With WrkSht
LNam = .Cells(CurrRow, "B").Value
GNam = .Cells(CurrRow, "A").Value
SexFld = .Cells(CurrRow, "C").Value
If IsNull(DLookup("Employees.FirstName", "Employees", "Employees.LastName = '" & LNam & "' And Employees.FirstName = '" & GNam & "'")) Then
SqlCmd = "INSERT INTO Employees " & _
"(FirstName, LastName, Sex) " & _
"VALUES " & _
"('" & GNam & "', '" & LNam & "', '" & SexFld & "')"
Else
SqlCmd = "UPDATE Employees " & _
"SET Employees.Sex = '" & SexFld & "' " & _
"WHERE Employees.LastName = '" & LNam & "' " & _
"AND Employees.FirstName = '" & GNam & "'"
End If
CurrentDb.Execute SqlCmd, dbFailOnError
End With
CurrRow = CurrRow + 1
Loop

WrkBk.Close SaveChanges:=False

Can I convert the SqlCmd commands to an access queries?

God Bless

Sami

 

[apr pillai]

Why struggle with the code when you can link excel data to Access (from Access) and create a Query from the linked table?

 

[samipm]

How do I do that?

 

[apr pillai]

First, you must ensure that the data you are trying to bring into Access are created conform to database rules in Excel. Search the topic DATABASE in excel to get documents listed about this subject and go through them.

There is a menu option in Access to Link external data sources including Excel. It is difficult to explain all the details here.