Solved Insert Into not working with a split database

[Eureka99]

Hi Chaps,

I have built a database that is using the sql code below to dump a list of components from a SQL data warehouse into the access database.
Whilst the access file is in one piece this works fine.

When I split the database into front and back end, it then falls over.

If a product has 14 components for example, it will enter 14 lines of the first component, instead of 14 lines of different components.

How do i resolve this when the database is split and all the tables are then links?

The table im dropping them into is tbl_temp. Mo1 is the MO number taken from an Input box field.

From SQL:

EIT_GB_MVXCDTA_MWOMAT - The table i'm getting the data from
VMMTNO - Item number
VMMFNO - MO number
VMMSEQ - Component sequence number

Dim db As DAO.Database
Set db = CurrentDb
db.Execute "Insert Into tbl_temp (Comp) Select VMMTNO From EIT_GB_MVXCDTA_MWOMAT Where VMMFNO=" & [Mo1] & " Order By VMMSEQ;", dbFailOnError
Set db = Nothing

 

[Minty]

If you run that SQL query as a SELECT statement when it's split, does it return the correct results?

 

[Eureka99]

No It doesnt.

I've tried it two more ways.

On the unsplit database I've used:

SELECT
VMMTNO
FROM EIT_GB_MVXCDTA_MWOMAT
WHERE [VMMFNO]=1744373
ORDER BY VMMSEQ;

which doesnt work and gets me the same list of 14 x 1 component.

I've checked it on MSSMS which gives me the full list

SELECT
VMMTNO
FROM [eit].[EIT_GB].[MVXCDTA_MWOMAT]
WHERE [VMMFNO]='1744373'
ORDER BY VMMSEQ;

N.B 1744373 is an active MO number that i've just hard coded as a test

 

[Minty]

It's text if you are putting ' ' around it in SSMS, you need to do the same in Access.

db.Execute "Insert Into tbl_temp (Comp) Select VMMTNO From EIT_GB_MVXCDTA_MWOMAT Where VMMFNO='" & [Mo1] & "' Order By VMMSEQ;", dbFailOnError

 

[Eureka99]

I put ' ' around it just as thats what the guy who taught me SQL taught me to do. It works without them.

Strangely in the query builder I seem to have fixed it. I removed by "Distinct" command and selected unique records from the options.

It gave me this code that now seems to be working. I dont know if you can tell me the difference:

db.Execute "Insert Into tbl_temp (Comp) SELECT DISTINCT EIT_GB_MVXCDTA_MWOMAT.VMMTNO FROM EIT_GB_MVXCDTA_MWOMAT WHERE (((EIT_GB_MVXCDTA_MWOMAT.[VMMFNO])= " & [Mo1] & "));", dbFailOnError

 

[CJ_London]

I dont know if you can tell me the difference:

without seeing what you actually had originally, impossible to say since selecting unique records just adds the word DISTINCT - which isn't in your original code. So perhaps you applied it incorrectly. Or perhaps you were returning VMMFNO and not VMMTNO as you are showing.

I presume the EIT_GB_MVXCDTA_MWOMAT table is a linked table to sql server and tbl_temp is a linked table to your accdb back end. So possible you were linked to the wrong table(s)?

The other possibility is your EIT_GB_MVXCDTA_MWOMAT does not have a compound unique index on VMMTNO and VMMFNO so there are repeats of the data - using DISTINCT will remove the repeats. However I would have expected more than 14 records to be returned without the DISTINCT if that is the number of unique records you are expecting.

 

[sonic8]

If a product has 14 components for example, it will enter 14 lines of the first component, instead of 14 lines of different components.

My bet: Your SQL Server source "table" is actually a view and you've specified an incorrect primary key while linking the view. They primary key column you specified returns multiple values per key value. These are the duplicate/incorrect records you are seeing.