Make Table Query and Relationships 1

[rlmorgan]

I need some help on this one also. I have two Crystal Report generated Excel spreadsheets that are auto-updated on a daily basis. One spreadsheet contains computer assets (type, model, memory, etc). The second spreadsheet has users assigned to them (names, phone numbers, location, etc). I need to import these in to existing tables with numerous filters into my Access database weekly. They don’t change a whole lot but I need the changes to be reflected in my database.

The way I have it setup now is through linked tables, AssetDump1 and AssetDump2. I then use a “Make table query” to create my two filtered tables, tbl_Asset and tbl_Users.

My problem is the filtered tables have relationships set up that I have to delete and then recreate every time I need to run the “Make table query” because they have to be deleted first.

Is there a better way to get the raw data into a table without breaking the relationship? I do not make any changes to these two tables. They are used to pull information only.

 

[PHV]

Don't recreate the tables, but delete all the records before an APPEND query (VBA code):
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tbl_Users"
DoCmd.RunSQL "DELETE FROM tbl_Asset"
DoCmd.RunSQL "INSERT INTO tbl_Asset SELECT * FROM AssetDump1"
DoCmd.RunSQL "INSERT INTO tbl_Users SELECT * FROM AssetDump2"
DoCmd.SetWarnings True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rlmorgan]

Thanks for the help. I now have it working.

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tbl_Users"
DoCmd.RunSQL "DELETE FROM tbl_Assets"
DoCmd.OpenQuery "qry_UpDtAssetTbl"
DoCmd.OpenQuery "qry_UpDtUserTbl"
DoCmd.SetWarnings True

I am having to call Append querys, this is ok but can I do it all using a VBA script? My query modifys as it filters.

Here is and SQL view of the Asset Append Query:

INSERT INTO tbl_Assets ( [Asset Tag], SerialNo, [Client Asset Tag], [Other Asset Tag], ComputerType, Model, Assignment, memComment, FullName, dtLastModif )
SELECT "PC" & [AssetTag] AS [Asset Tag], [AssetDump1].[SerialNo], [AssetDump1].[Client Asset Tag], [AssetDump1].[Other Asset Tag], [AssetDump1].[ComputerType], Right([Model_Name],Len([Model_Name])-9) AS Model, [AssetDump1].[Assignment], [AssetDump1].[memComment], [AssetDump1].[FullName], [AssetDump1].[dtLastModif]
FROM AssetDump1
WHERE ((("PC" & [AssetTag]) Like "PC11******" Or ("PC" & [AssetTag]) Like "PC12******") And (([AssetDump1].[ComputerType])="Laptop" Or ([AssetDump1].[ComputerType])="Desktop") And (([AssetDump1].[Assignment])<>"retired") And (([AssetDump1].[Model_Name])<>"PRECISION 220") And (([AssetDump1].[Manufacturer])="Dell"))
ORDER BY "PC" & [AssetTag];

The area in Bold is where I am having trouble in VBA, I need the "" to combine the "PC" to the AssetTag field. Is there a way around this, or should I stay with calling the Append Querys?

 

[PHV]

Replace all double quotes in the SQL code with single quotes:
SELECT 'PC' & [AssetTag] AS [Asset Tag] ...
WHERE ((([AssetTag]) Like '11*' Or ([AssetTag]) Like '12*') ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rlmorgan]

I tried change the all of the
but now I am getting a :
Syntax error (missing operator) in query expression '((('PC & [AssetTag]) Like 'PC11******' Or ...

It may be more trouble than it's worth to troubleshoot it.

Thanks for the help