Access To Sql

[bandkindy]

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\customers.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.

[PianoMan64]

Ok, the first thing that you need to understand is that if you want to copy records from one table to another table that has the same structure, you simply use a Append Query, if all your doing is adding additional records to the already existing data that is currently in the SQL Tables.

You do want to make sure that if you're adding records that there is somekind of Primary key to keep what has already been added from being added again to avoid duplication.

This is where you could create a No Match Query and base it on the Primary Key of each table of the Respective DATA tables that you have both in Access and MS SQL.

Without seeing more of your code that you have in place, I can't really give you a more detail answer than what I've already given.

If you want to send me a message with all your Queries and code seqments, then I can take a look at what you have a give you a better idea as to how to proceed.

Thanks,

Joe P.

[NeoPa]

No problems with PMs per-se, but we prefer if the technical communications go on visibly in the thread. Otherwise the thread is harder to follow for someone else looking for solutions to their problem.