469,571 Members | 1,276 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,571 developers. It's quick & easy.

Access To SQL

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

Set rs = Nothing
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.
Feb 25 '08 #1
1 1353
759 Expert 512MB
There is a tool "tablediff.exe" in sql server 2005 to sync the tables between two databases. You can use that.
Feb 28 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

63 posts views Thread by Jerome | last post: by
13 posts views Thread by Simon Bailey | last post: by
reply views Thread by Frederick Noronha \(FN\) | last post: by
64 posts views Thread by John | last post: by
17 posts views Thread by Mell via AccessMonster.com | last post: by
37 posts views Thread by jasmith | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.