outlook --> Access vba question

[blastman]

hi guys,

I'm writing some VBA code in outlook and I'm after a little help.
In access, I have a database called "Customers" with one table within it called "New Customers" This table has 9 fields, first-name, last-name, home number, mobile number, appointment date, appointment time, city and description.

In my outlook code I have 9 filled variables called, lanme, fname, homenum, mobile, apdate, aptime, city and descript. (which are pulled from a FTP site after a email comes in with a certain subject) I like to be able to fill the fields of my database with these variables from outlook.

This is even possible???

Can anyone point me in the right direction??

[blastman]

Ok,
So i've created a connection in ODBC, and used the code below to connect to it.


Set objADOConn = CreateObject("ADODB.Connection")
objADOConn.Open "DSN=ocs"


As I understand it, I'm suposed to use a recordset and a SQL statement. Sad thing is, i have no ideas what either of these are/should be !!!

[Mavyak]

Sub FillVariables()
On Error Goto errHandler
Dim c As ADODB.Connection

Set c = New ADODB.Connection
With c
.ConnectionString = "" 'Enter your connection string here
.Open
.Execute getSQL()
.Close
End With
ExitSub:
If c.State <> adStateClosed Then
c.Close
End If
Set c = Nothing
Exit Sub
errHandler:
If c.Errors.Count > 0 Then
Debug.Print "ADO Error " & c.Errors(0).NativeError & ": " & c.Errors(0).Description
Else
Debug.Print "Outlook Error " & Err.Number & ": " & Err.Description
End If
Resume ExitSub
End Sub
Private Function getSQL() As String
Dim strSQL As String
'Only 8 fields here. Post says there should be 9.
strSQL = "INSERT INTO [New Customers](" & vbCr
strSQL = strSQL & " [first-name]," & vbCr
strSQL = strSQL & " [last-name]" & vbCr
strSQL = strSQL & " [home number]" & vbCr
strSQL = strSQL & " [mobile number]" & vbCr
strSQL = strSQL & " [appointment date]" & vbCr
strSQL = strSQL & " [appointment time]" & vbCr
strSQL = strSQL & " [city]" & vbCr
strSQL = strSQL & " [description]" & vbCr
strSQL = strSQL & "VALUES(" & vbCr
strSQL = strSQL & " """ & lanme & """," & vbCr 'Check your variable spelling
strSQL = strSQL & " """ & fname & """," & vbCr
strSQL = strSQL & " """ & homenum & """," & vbCr
strSQL = strSQL & " """ & mobile & """," & vbCr
strSQL = strSQL & " """ & apdate & """," & vbCr
strSQL = strSQL & " """ & aptime & """," & vbCr
strSQL = strSQL & " """ & city & """," & vbCr
strSQL = strSQL & " """ & descript & """)" & vbCr

getSQL = strSQL
End Function
See how that does for ya. You'll need to set a reference to "Microsoft ActiveX Data Objects 2.8 Library" to use the code above. To do that, go to Tools-->References, and then place a check in the appropriate box.