Bdtjtk

I know this is a bit silly one, but I have a question on how to update an offline database.

I have various databases in the same folder (they are all offline and redistributable - being an analytic tool to be used with csv and txt files produced by SAP.) It is crucial for the company for this tool to be offline and operated by VBA for Access + SQL. I am trying to bypass the 2GB limit by employing various databases. One of them is a central database where I have forms and run the code. I have various join queries and that create new tables that are used for reports etc.

How can I join two tables (existing ones) in a remote offline database (existing one) and insert it into a new table (not existing) in a database (existing one)?

Sub adddatesALT()

Dim sDate As String

DoCmd.SetWarnings False

Dim ssql as String

dbinput is database called UNION with tables AGR_USERS_ALL, USR_02_ALL and that's where the tables are (FROM)

dboutput is the database called MASTER and that's where I want to insert the tables (IN)

dbinput = "[" & Application.CurrentProject.Path & "UNION.accdb" & "]"

dboutput = "[" & Application.CurrentProject.Path & "MASTER.accdb" & "]"

Short one - how to incorporate the path into an update query?

DoCmd.RunSQL ("UPDATE AGR_USERS_ALL SET AGR_USERS_ALL.JNC_STATUS = 'ACTIVE' WHERE ((AGR_USERS_ALL.TO_DAT) > (AGR_USERS_ALL.CH_DATE));")

And the major one - how to incorporate it the path into a more complex query as this one

ssql = "SELECT AGR_USERS_ALL.AGR_NAME, AGR_USERS_ALL.UNAME, AGR_USERS_ALL.FROM_DAT, AGR_USERS_ALL.TO_DAT, AGR_USERS_ALL.COL_FLAG, AGR_USERS_ALL.JNC_STATUS, USR_02_ALL.JNC_STATUS, USR_02_ALL.USTYP, USR_06_ALL.LIC_TYPE INTO USR02_AGR_ACTIVE_ROLES"

ssql = ssql & " FROM (AGR_USERS_ALL INNER JOIN USR_02_ALL ON (AGR_USERS_ALL.UNAME = USR_02_ALL.BNAME) AND (AGR_USERS_ALL.[SYSTEM NO] = USR_02_ALL.[SYSTEM NO])) INNER JOIN USR_06_ALL ON (USR_02_ALL.BNAME = USR_06_ALL.BNAME) AND (USR_02_ALL.[SYSTEM NO] = USR_06_ALL.[SYSTEM NO])"

ssql = ssql & " WHERE (((AGR_USERS_ALL.COL_FLAG) Is Null) AND ((AGR_USERS_ALL.JNC_STATUS)<>'Expired') AND ((USR_02_ALL.JNC_STATUS)<>'Expired') AND ((USR_02_ALL.USTYP)<>'B' And (USR_02_ALL.USTYP)<>'L'));"

DoCmd.RunSQL ssql

You have multiple ways to do this

1. Use the IN Clause

For the short one that gives

DoCmd.RunSQL ("UPDATE AGR_USERS_ALL IN '" & dboutput  & "' SET AGR_USERS_ALL.JNC_STATUS = 'ACTIVE' WHERE ((AGR_USERS_ALL.TO_DAT) > (AGR_USERS_ALL.CH_DATE));")

2. 
   Link the external tables.

This is for me the best solution. Linked tables are one of the strengths of Access, and your queries will stay simple.

To do it efficiently, prefix the linked table name with its database name so you always know from which DB is a table

For the short one that gives

DoCmd.RunSQL ("UPDATE MASTER_AGR_USERS_ALL SET MASTER_AGR_USERS_ALL.JNC_STATUS = 'ACTIVE' WHERE ((MASTER_AGR_USERS_ALL.TO_DAT) > (MASTER_AGR_USERS_ALL.CH_DATE));")

Use exactly the same tricks for the "major one". It doesn't matter wether its an UPDATE or SELECT statement, or if its a big or short query.

Thank you very much!

I found a solution in the meantime, but I will also follow your advice ;)

My solution is:

Sub USR02AGRJOIN()

Dim ssql As String

Dim ssql2 As String

DoCmd.SetWarnings False



Dim dbinput As String

Dim dboutput As String



dboutput = "[" & Application.CurrentProject.Path & "MASTER.accdb" & "]"

dbinput = "'" & Application.CurrentProject.Path & "UNION.accdb" & "'"



ssql = "SELECT AGR_USERS_ALL.AGR_NAME, AGR_USERS_ALL.UNAME, AGR_USERS_ALL.FROM_DAT, AGR_USERS_ALL.TO_DAT, AGR_USERS_ALL.COL_FLAG, AGR_USERS_ALL.JNC_STATUS, USR_02_ALL.JNC_STATUS, USR_02_ALL.USTYP, USR_06_ALL.LIC_TYPE INTO " & dboutput & ".USR02_AGR_ACTIVE_ROLES"

ssql = ssql & " FROM (AGR_USERS_ALL INNER JOIN USR_02_ALL ON (AGR_USERS_ALL.UNAME = USR_02_ALL.BNAME) AND (AGR_USERS_ALL.[SYSTEM NO] = USR_02_ALL.[SYSTEM NO])) INNER JOIN USR_06_ALL ON (USR_02_ALL.BNAME = USR_06_ALL.BNAME) AND (USR_02_ALL.[SYSTEM NO] = USR_06_ALL.[SYSTEM NO]) IN " & dbinput & ""

ssql = ssql & " WHERE (((AGR_USERS_ALL.COL_FLAG) Is Null) AND ((AGR_USERS_ALL.JNC_STATUS)<>'Expired') AND ((USR_02_ALL.JNC_STATUS)<>'Expired') AND ((USR_02_ALL.USTYP)<>'B' And (USR_02_ALL.USTYP)<>'L'));"

DoCmd.RunSQL ssql



End Sub

I have just struggled with syntax :)

Thank you once again!