Excel Coding to Save To Database

[DYM03]

Any help would be appreciated ASAP. I have set up a template to save the data to a database in Access. I have a command button to display a message box with the data the user is about to update. When okay is clicked I want the message box to display Microsoft's 'Save To Database File' Box. It is the box that give options to create new, update, or continue without updating. I can't seem to get my own code to work to save the data to the database file so I would like to use Microsoft's. I have tried using Thisworkbook.Close, ActiveSheet.Quit, etc. so that the box will display without clicking the 'X' to close. Do you have any suggestions? Any help would be appreciated. I hope I have explained myself.

 

[SkipVought]

Hi,

Please post your Update/Insert routine.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DYM03]

I deleted the code I was attempting to use because I continued to get all types of processing error and decided to just used MS template update. To close the database without using the X button I have tried .Close and .Quit methods but they won't display the message box to Save To Database. I can add the close button to a menu but I need code to do the same thing so that I can add it to the command button.

I am used to coding in Access but I am not sure what procedure to use with Excel.

 

[DYM03]

**I need the same information displayed in the message box below to be written to and Excel or Access database.**

Private Sub CommandButton1_Click()
Dim SSN As String
Dim Msg As String
Dim PerFrom As String
Dim PerTo As String
Dim Reg, Otp, Vac, Sck, Hol, Lwo, Lwp, Total As String


SSN = Range("EMP_NUMBER&quot.Value
PerFrom = Range("L7&quot.Value
PerTo = Range("N7&quot.Value
Reg = Range("H38&quot.Value
Otp = Range("I38&quot.Value
Vac = Range("J38&quot.Value
Sck = Range("K38&quot.Value
Hol = Range("L38&quot.Value
Lwo = Range("M38&quot.Value
Lwp = Range("N38&quot.Value
Total = Range("O38&quot.Value

Msg = "Please Verify Data Below. If is not correct click CANCEL to make changes."
MsgBox Msg & Chr(13) _
& " " & Chr(13) _
& "Employee Number " & SSN & Chr(13) _
& " " & Chr(13) _
& "Pay Period " & PerFrom & " " & PerTo & Chr(13) _
& " " & Chr(13) _
& "Regular Hours " & Reg & Chr(13) _
& " " & Chr(13) _
& "Overtime Hours " & Otp & Chr(13) _
& " " & Chr(13) _
& "Vacation Hours " & Vac & Chr(13) _
& " " & Chr(13) _
& "Sick Hours " & Sck & Chr(13) _
& " " & Chr(13) _
& "Holiday Hours " & Hol & Chr(13) _
& " " & Chr(13) _
& "Lost W/ Pay " & Lwo & Chr(13) _
& " " & Chr(13) _
& "Lost W/O Pay " & Lwp & Chr(13) _
& " " & Chr(13) _
& "Total Hours To Be Paid " & Total & Chr(13) _
& " " & Chr(13) _
& " ", vbOKCancel
'Ok will generate second message box to confirm time before submitted. Cancel
' will take user back to time sheet.
'Ok on that box will save to database file to be uploaded into Infinium

 

[SkipVought]

DYM,

I am confused.

First, there is NO Save to database. You don't SAVE to a database. You Insert new rows or you Update existing rows.

Where is your Insert/Update code?

Are you using DAO or ADO?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DYM03]

DAO. I will look through my diskettes to see if I saved an extra copy of the code.

 

[DYM03]

I couldn't find any old data but I do remember trying something similar to this. Now I keep getting an error telling me that I have an unrecognized database format. Can you help me with this?


Sub AppendFile()

'Workbooks.OpenDatabase Filename:="C:\Documents and Settings\user\My Documents\db1.mdb"

Dim db1 As Database
Dim rstEmployees As Recordset

Set db1 = OpenDatabase("Employee Time.mdb&quot

Set rstEmployees = db1.OpenRecordset("EmployeeTime", dbOpenDynaset)

With rstEmployees
.AddNew
!FirstName = rstEmployees!FirstName
!LastName = rstEmployees!LastName

End With


End Sub


Private Sub CommandButton2_Click()
Call AppendFile
End Sub