By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,949 Members | 1,428 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,949 IT Pros & Developers. It's quick & easy.

Access To Sql

P: 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....
Expand|Select|Wrap|Line Numbers
  1. Sub DAOFromExcelToAccess()
  2. ' exports data from the active worksheet to a table in an Access database
  3. ' this procedure must be edited before use
  4. Dim db As Database, rs As Recordset, r As Long
  5.  
  6.  
  7.     Set db = OpenDatabase("\\tbpdc01\d$\apps\VersionXX\Data\customers.mdb")
  8.     ' open the database
  9.     Set rs = db.OpenRecordset("Data", dbOpenTable)
  10.     ' get all records in a table
  11.     r = 2 ' the start row in the worksheet
  12.  
  13.         With rs
  14.             .AddNew ' create a new record
  15.             ' add values to each field in the record
  16.             .Fields("NAMECUST") = Range("A" & 68).Value
  17.             .Fields("AUDTDATE") = Range("A" & 69).Value
  18.             .Fields("AUDTTIME") = Range("A" & 69).Value
  19.             .Fields("TEXTSTRE1") = Range("B" & 7).Value
  20.    'there are additional fields as above, but no need to include all for purpose of short post in forum
  21.  
  22.       ' add more fields if necessary...
  23.             .Update ' stores the new record
  24.  
  25.               End With
  26.         r = r + 1 ' next row
  27.  
  28.     rs.Close
  29.     Set rs = Nothing
  30.     db.Close
  31.     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
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 374
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.
Feb 26 '08 #2

NeoPa
Expert Mod 15k+
P: 31,758
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.
Feb 26 '08 #3

Post your reply

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