First off, let me state this problem is for VBA within an Access app. Now, this is a sub-query of a much longer query, but I want to get the fundamentals down before I attempt to construct an even larger query.
For whatever reason, the following lines of code successfully opens a DAO.Recordset that I can loop through, perform operations on:
Dim testRS As DAO.Recordset
testQuery = "SELECT * FROM midsecurity_city
INNER JOIN itincity ON midsecurity_city.cityid4security = itincity.city_id;"
Set testRS = CurrentDb.OpenRecordset(testQuery)
This is comparing two columns of type BIGINT, across two different tables within the two different schemas.
Now, the following also compares two columns of type VARCHAR within two different tables, which are also contained within two different schemas. I include all these details, as I'm fairly new to Access, and am unsure if this information has an impact on my issue. For what it is worth, I'm reaching into a Postgres DB.
This query gives me an "Error 13: Type Mismatch" message and then cancels execution.
Dim testRS As DAO.Recordset
testQuery = "SELECT * FROM itincity INNER JOIN itinerary ON itincity.city =
itinerary.city;"
Set testRS = CurrentDb.OpenRecordset(testQuery)
My assumption is that Access really hates JOINS that involve anything other than an INTEGER comparison of two columns? Am I close to being accurate in this? If so, is there a way around this restriction, or is there another way entirely to approach something like this? Any educational material on how JOINS work within Access would also be appreciated, as they don't seem to play by the same rules as running a query in pgAdmin does.
Thanks for the help. I've been looking into this issue for about a week now.
Well, the "solution" seemed to be as I expected. Access simply hates JOINS that involve text-based columns. As a work-around, I added a foreign key (FK) to the table that seemed most dependent upon the other. This FK column was designated as type "BIGINT". Now, my massive query with numerous INNER JOINS (all of which now use bigints as well) works with ease.
However, I'm still curious as to why Access rejects textual JOINS... Seems like it should be perfectly acceptable.