Seems simple now that that you point it out to me. Thanks.

CREATE TABLE #Destination

(

Account char(1),

ReportDate datetime,

DataType varchar(5),

Data int

)

CREATE TABLE #Staging

(

Account char(1),

ReportDate datetime,

DataType varchar(5),

Data int

)

INSERT INTO #Destination

SELECT 'A', '2/29/2012','Type1',5 UNION

SELECT 'A',...

I want to delete and insert. It looks like your second statement is what I need.

Merge won't work since there's not a one-to-one relationship between deleted and inserted records.

That doesn't seem to insert any records. All records in the staging will be inserted.

My issue is I need to delete all records from the destination table where the...

Is EAV an option? The users wouldn't actually be creating tables, they would be creating metadata about their 'tables'. The metadata and the actual data could be stored in the...

Do you have service packs installed?

Are you building a Select statement to be used in an SSIS import package?

Can you do the logic in your SQL statement rather than a derived column within the package?

>> Is the module automatically fired when the access FE is opened?

Not in the method I describe. Its more of an access question so you might have better luck...

With the method I describe the connections are persistent and I only manually invoke the code when I am making design changes on the backend. In a development phase I...

I am not familiar with that OnOpen event method. I think that was Stephen_W_Dodd's method.

My method is a manual process to update the tables when changes are made to the...

You need to open the immediate window press CTRL+G it or access it from the View menu.

The copy and paste the Fixceonnections bit and hit enter.

In the immediate window paste in:

FixConnections "YourDatabaseServer", "YourDatabase"

Then hit enter.

No need for apologies J Livingston SQL. Your method is certainly more sophisticated than mine and is a method I will certainly consider.

Create a new module and add everything from code start to code end. Save the module. I call mine modDNSLessConnection.

You'll see the module tab along with the other Access objects,...

Create a new module and add everything from code start to code end. Save the module. I call mine modDNSLessConnection.

You'll see the module tab along with the other Access objects,...