2
To preface, I am pretty green on the whole sql thing, but am learning quickly so please bear with me.
I have an excel file that is copied to 5 users directories so they have their own working copy. This file is populated by the user. There is a macro that saves the file and names it based on specific data the user enters (first last name). I have that working fine. I also have created a macro that dumps the data to an Access 2007 db named DATA when the save button in the XLS file is clicked. That is working fine as well and I have included a copy of that script....
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("\\tbpdc01\d$\apps\VersionXX\Data\cus tomers.mdb")
' open the database
Set rs = db.OpenRecordset("Data", dbOpenTable)
' get all records in a table
r = 2 ' the start row in the worksheet
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("NAMECUST") = Range("A" & 68).Value
.Fields("AUDTDATE") = Range("A" & 69).Value
.Fields("AUDTTIME") = Range("A" & 69).Value
.Fields("TEXTSTRE1") = Range("B" & 7).Value
'there are additional fields as above, but no need to include all for purpose of short post in forum
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
This works fine and has presented no issues. Now this is where i run into issues. Ultimately, I have a SQL DB named TBPDAT that has a table named dbo.ARCUS in it. My customers.accdb file and the dbo.ARCUS file have an identical table structure and have identical column names and are laid out identically. I have a data connection named TBPDAT2 that gets me into the dbo. ARCUS table no problem. This has stumped me for a week.....I need to get the data from the DATA table in the CUSTOMERS.ACCDB file into dbo.ARCUS table. Again, column headings are the same and if I do a cut and paste from DATA in CUSTOMERS.ACCDB to the linked table dbo.ARCUS it works fine.
I am doing this because my customer is entering data into this spreadsheet. It creates forms for them automatically and works great. But I am trying to eliminate steps for them and dump this info also into their accounting software (ACCPAC ERP). The excel to access to sql was the best I could come up with. How can i make this access database update the same fields in the sql db TBPDAT with the table dbo.arcus being the target and do it in real time without any addtl user intervention. In other words take the data that dumps to access and put it in sql automatically as it arrives into the access db?
Please help.
