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??Ok,

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

Code: [Select]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 !!!This Scripting Guy article should show you a generalized method. Most of the code is boilerplate. You need to fill in the particulars.

Code: [Select]objConnection.Open "DSN=ocs"

objRecordSet.Open "SELECT * FROM Employees", _ 'name of table to update
objConnection, adOpenStatic, adLockOptimistic

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt") 'name of FTP file

Code: [Select] objRecordSet.AddNew
objRecordSet("db lname") = ftp lname
objRecordSet("db fname") = ftp fname
objRecordSet("db home num") = ftp home num
objRecordSet.Update

The recordset methods do all the heavy lifting. If you have any questions be sure to post back.

Good luck. Fantastic!!!

I had to jiggle a few things round.

I already had a function to read values from txt files, so used that instead.

But it work's like a CHARM!!

Thaking you!!




Code: [Select]Private Sub update_access()

Fname = GetFileContent("C:\FTPfeed\fname.txt")
Lname = GetFileContent("C:\FTPfeed\lname.txt")
email = GetFileContent("C:\FTPfeed\email.txt")
homenum = GetFileContent("C:\FTPfeed\homenum.txt")
mobile = GetFileContent("C:\FTPfeed\mobile.txt")
APdate = GetFileContent("C:\FTPfeed\APdate.txt")
APtime = GetFileContent("C:\FTPfeed\APtime.txt")
city = GetFileContent("C:\FTPfeed\city.txt")
descript = GetFileContent("C:\FTPfeed\descript.txt")

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

Set objRecordSet = CreateObject("ADODB.recordset")

objRecordSet.Open "SELECT * FROM data ", objADOConn, adOpenStatic, adLockOptimistic

objRecordSet.AddNew
objRecordSet("LastName") = Lname
objRecordSet("FirstName") = Fname
objRecordSet("HomePhone") = homenum
objRecordSet("EmailAddress") = email
objRecordSet("Appointment Date") = APdate
objRecordSet("Appointment Time") = APtime
objRecordSet("City") = city
objRecordSet("Description") = descript
objRecordSet("MobilePhone") = mobile
objRecordSet.Update

End Sub