output excel value to access table

[MikeCDPQ]

I am using an Excel user form with a calendar and I need to output the selected date to an access table.

I have everything else, all I am missing is the instruction to replace the 1 value in my access table with the value in the excel spreadsheet.

Can one have an update query in access that uses a variable stored in a script as an UPDATE TO value ???

Any suggestions will be appreciated.

Thanks,

Mike

 

[SkipVought]

Mike,

Are you using ADO or DAO or what?

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[MikeCDPQ]

Skip,

I am using DAO .

Mike

 

[SkipVought]

   With rst
      .Edit
      !MyDate = MyExcelValue
      .Update
   End With

where rst is a recordset that you want to update.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[MikeCDPQ]

Skip,

Im not very familiar with VB Access.

What is RST to be replaced by ?

Here is what I have so far, i know I am m missing the reference to my access table ("DATE") but I don't even know how to refer to it.

Function UpdateDateTable()


Dim SelectedDate As Date
Dim AccessDate As Date
Dim DateTable As TableDef

Set calendardate = GetObject("G:\Department Shared Data\Purchasing\Analysis\Banner Aged Reports\CalendarForm.xls")

AccessDate = calendardate.Sheets("sheet1").Range("A1").Value

With rst
.Edit
!SelectedDate = calendardate
.Update
End With


Application.CloseCurrentDatabase
Application.Quit acExit
Set Application = Nothing


End Function

 

[SkipVought]

You have to open a recordset (rst)

  Set db = OpenDatabase(YourAccessDatabase)
  SqlToSelectRecordsToUpdate = "Select...From...Where..."
  Set rst = db.OpenRecordset(SqlToSelectRecordsToUpdate, dbOpenDynamic)

AccessDate = calendardate.Sheets("sheet1").Range("A1").Value

    With rst
      .Edit
      !SelectedDate = AccessDate 
      .Update
    End With

  rst.close
  db.close

  Set rst = nothing
  Set db = Nothing

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[SkipVought]

BTW,

If you don't have DAO HELP, search for
[tt]
DAO360.CHM
[/tt]
on your C drive.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[MikeCDPQ]

Skip,

Thanks for taking the time to help me.

Unfortunately, not being overly familiar with VB for access, I could not get your script to work. I'm sure that someone with more experience would have caught on right away.

Since my access table will always only have only 1 record (a date) I decided to instead have a linked table to the excel spreadsheet where the date is saved. It works beautifully.

Thanks again for your help, always appreciated.

Sincerely,

Mike