Check if 2nd table does not contain records from the first table. (1 Viewer)

QMDirk

Member
Local time
Today, 12:13
Joined
Nov 16, 2019
Messages
52
I am importing data from an Excel spreadsheet to a table and overwriting the same table each time the data is imported. I have another table, with identical structure, field names etc., that I want to transfer the new imported data to, however, I want to check if the record already exists in the second table before adding the data to the table. Right now, I can open both tables, select one or multiple rows from the first table, copy the record(s), then click on the next empty row in the second table and paste all the records. Everything works fine. But I want to perfom this checking, copying, and pasting through VBA. Any suggestions?
 

vba_php

Forum Troll
Local time
Today, 14:13
Joined
Oct 6, 2019
Messages
2,884
QM,

It kind of sounds like you have too many un necessary steps in your current process. for instance why are you overwriting table 1 every time? why do you have 2 identical tables? what about a linking scenario? and yes, importing, copying, pasting, and comparing can all be done with code. but you might be able to repair the current process you're going through without even needing to do that.

<edit>
try Colin's stuff before you try any of my whacked out suggestions. I'm not an expert.
 

QMDirk

Member
Local time
Today, 12:13
Joined
Nov 16, 2019
Messages
52
The excel spreadsheet is an ever-changing "temporary" production schedule. If I link the tables, then the data disappears when the spreadsheet is altered. I need to be able to store the data permanently in an Access table. Then that data will be displayed on Production Forms to record the plant's daily production.
 

vba_php

Forum Troll
Local time
Today, 14:13
Joined
Oct 6, 2019
Messages
2,884
If I link the tables, then the data disappears when the spreadsheet is altered.
I never linked tables much, and never participated much in shared workbooks on a network, but under what circumstance are you seeing the data disappear? I ran a test with an accdb and an xlsx under multiple scenarios and the data in access never disappeared as I was editing the excel sheet, regardless of the transaction point I was doing it at.
I need to be able to store the data permanently in an Access table. Then that data will be displayed on Production Forms to record the plant's daily production.
are you using access forms to input data instead of in an xl sheet because it's more user friendly for your people? if all else fails and you can't find an alternative to solve the problem, almost anyone around here can provide sample vba scripts to the solve the problem. that wouldn't take much work i wouldn't think.
 

Users who are viewing this thread

Top Bottom