I've got a summer internship and was asked to modify and add features to an already existing MS Access DB. I don't actually take databases until next semester, so I was just kinda thrown into the fire here with a "here are the backup files, figure it out, we don't really care" mentality. The DB admins don't seem eager to help me at all honestly (union factory problems? idk).
Long story short, after lots of research and googling I think I've figured everything out pretty well now and am ready to deploy the newly updated db. I'm not sure how to best do this though, and would like some opinions on my current approach so far.
It's a shared user environment. I think I want to split the DB where everyone will have their own copy of a front-end DB file on our local network server, which is linked to a single backend db file also on the local network server.
Keep in mind, I haven't experimented with this next idea at all, and it's exactly that; just an idea I have for how this whole thing will work.
There's a replicated server that I have access to which runs some form of SQL Server. I'm currently using a pass-through query to pull the required data from this server. I have an update query
SELECT * INTO myMainTable FROM myPassThru(uploadQuery)
which I'm planning on having run after the front page form of the DB is opened (and after the myMainTable's contents are deleted via VBA script SQL command). So every time a user opens their front-end db, myMainTable will be updated with the most up-to-date records from our replicated server. Will this work?
Maybe just having a linked ODBC table would be better, and then make a query to pull the required records and columns from it (the replicated db has wayyyyyy more fields and records than I actually need to pull)?
I've added 6 linked tables that I need to pull from and join together into my database. I'm now trying to use the same code from my old working pass-through query but with modified table names to reference the linked tables ('_dbo' appended to front). I'm getting a syntax error now when I try and save the query that my form uses stating
Syntax error (missing operator) in query expression 'dbo_orsrg.afldat "FulfillmentDate"'
My code is below:
SELECT dbo_orsrg.afldat "FulfillmentDate", dbo_orkrg.syscreated "SODate" , dbo_orkrg.docnumber "CusOrdNum", dbo_orkrg.ID "ID", dbo_orkrg.syscreator, dbo_orsrg.ordernr "SONum1", dbo_orsrg.regel "LineNum", dbo_orkrg.debnr "ShipTo1", c1.cmp_name "Name" ,dbo_orkrg.debnr AS "CUNum2", dbo_orkrg.ordbv_afgd AS Confirmed1, hr.fullname, dbo_Items.Class_03 As "Product Line", dbo_Items.UserField_04 AS "BPEX Code", dbo_Items.Description_0 AS [Description], (CASE WHEN dbo_Items.IsFractionAllowedItem = 0 THEN ROUND(dbo_orsrg.esr_aantal,0) ELSE dbo_orsrg.esr_aantal END) AS "ToBeDelivered1" , dbo_orkrg.represent_id "CSRep", dbo_orsrg.requesteddate "RequestDate",dbo_orkrg.cscredhold,dbo_orsrg.CSFulfillDateRequired, dbo_orsrg.prijs_n, dbo_orsrg.esr_aantal, dbo_orsrg.magcode "WH#", dbo_orkrg.valcode, dbo_orkrg.koers
FROM dbo_orsrg WITH (NOLOCK)
INNER JOIN dbo_orkrg WITH (NOLOCK) ON dbo_orkrg.ordernr = dbo_orsrg.ordernr
INNER JOIN dbo_humres hr WITH (NOLOCK) ON hr.res_id = dbo_orkrg.syscreator
INNER JOIN dbo_cicmpy c1 WITH (NOLOCK) ON c1.debnr = dbo_orkrg.debnr
INNER JOIN dbo_cicmpy c2 WITH (NOLOCK) ON c2.debnr = dbo_orkrg.verzdebnr
INNER JOIN dbo_Items WITH (NOLOCK) ON dbo_orsrg.artcode = dbo_Items.ItemCode AND NOT Type IN ('C','P')
INNER JOIN dbo_grtbk WITH (NOLOCK) ON dbo_grtbk.reknr = dbo_Items.GLAccountDistribution
LEFT OUTER JOIN ( SELECT SUM(aant_gelev) AS Received, ordernr AS PoNum, artcode FROM dbo_orsrg GROUP BY ordernr, artcode ) AS recv
ON dbo_orsrg.purchaseordernr = recv.PoNum AND recv.artcode = dbo_orsrg.artcode AND dbo_orsrg.purchaseordernr IS NOT NULL AND recv.PoNum IS NOT NULL And recv.artCode IS NOT NULL AND dbo_orsrg.artcode IS NOT NULL
WHERE dbo_orsrg.esr_aantal > dbo_orsrg.aant_gelev
AND dbo_orsrg.uitgifte = 0
AND NOT dbo_orsrg.artcode IS NULL
AND dbo_orkrg.ord_soort IN ('V','P','N')
AND dbo_orkrg.status='V'
AND dbo_orkrg.afgehandld=0
AND dbo_orsrg.magcode in ('1224','1223','1222','1221','1220', '1219','1218','12z3','12ZB','12zl') AND (dbo_orkrg.selcode <> '98' OR dbo_orkrg.selcode IS NULL) ORDER BY dbo_orkrg.afldat, dbo_orsrg.ordernr, dbo_orsrg.artcode
1 Answer 1
You are trying to execute SQL SERVER sql syntax in msaccess. Need to remove the nolock hints. The column and table alias need to b prefixed with AS. And more. Executing your query as a pass-through is probably a better option anyway.