Link Data to Access DB

[royal presence]

Hi,

I created an .xlt Template. I had it connecting to Access

Connectivity was lost a month ago. Anyway, during the downtime, a coworker insert some code to save the file as an .xls, which is fine.

However, we lost the ability to use the template saving a record to a database. At any rate, upon manual file, save, the record now gets saved to an excel file. I changed the template to save to an excel, then import to Access. Also, it gets saved as an individual xls file to a specified location in a folder.

I don?t have the code at home now, but we can?t make it work both ways simultaneously saving the individual file and as a record in the DB at the same time.

Is there any way to do both?

Thank you

[Ken Puls]

Hi there,

I'm not sure I follow you 100%, so if I miss the mark, I apologize. If you want to write a new record to an Access table just before you save your workbook, you could try modifying the following to suit your needs:


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Macro purpose: To add record to Marina Access database using ADO and SQL
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDB As String
' Set the string to the path of your database
strDB = "D:\My Documents\Access Files\Marina.mdb"
' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
' Open recordset based on Moorages table
rst.Open "INSERT INTO Moorages (CustID,Type,DatePaid,DateStart,DateEnd,Amount)" _
& " VALUES ('zz-01','Prorate',#1/15/03#,#1/15/04#,#2/28/05#,500)", cnt
' Close ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
End Sub

This code would go into the ThisWorkbook module. The hardest part would be modifying the SQL statement to get it to match your table structure, so a breakdown follows:


"INSERT INTO Moorages (CustID,Type,DatePaid,DateStart,DateEnd,Amount)" _
& " VALUES ('zz-01','Prorate',#1/15/03#,#1/15/04#,#2/28/05#,500)"

-Replace "Moorages" with your table's name
-Replace "CustID,Type,DatePaid, etc..." with the column headings that you want to fill on your table. I believe that you should only need to put in the columns that you actually want to fill with data, providing that any you don't care about can accept NULL values or have default values
-Change my info in the values section to match what you want. Make sure that you separate each field with , surround text with ', dates with #, and values need no markings

This can be coded to accept values directly from your worksheet cells as well. More information would be needed to accomplish that though.

Hope this helps,

Ken

[Ken Puls]

Oops,

I also forgot to mark one critical component... the above code will error out if a reference is not set to the Microsoft ActiveX Data Objects Library. You can do this by going into the VBE, choosing Tools|References, and scrolling down the list to find it. I don't know what version of Office you're running, but in Office 2003, its the Microsoft ActiveX Data Objects 2.7 Library.

Cheers,

[bkudulis]

I am trying to pass the values from the worksheets cells. I thought that I could just make reference to them by entering the cell that I wanted but I have received a syntax error. How does that work?

[Ken Puls]

Sorry, my notification email somehow got filed without me reading it...

Can you post an example (or upload a sample file) of your data? It would help to know where your data resides in the sheet, and what the column headers for your Access tables are.

If you can give me that info, I can modify it to work for you.

Cheers,