I'm nearing the end of my PostGreSQL migration from Access/Access to
Access/PostGreSQL and I wanted to let everyone know how it is going.
1) ADO and access do not go well together, at least not in access 2000.
Recordsets are not updatable, the internal access sort and filter functions
work sporadically and weird.
2) I had initially converted those forms that didn't need to be updatable to
ADO and kept the rest DAO, but that didn't work out so well, so now I have
taken a step backwards and changed everything to DAO. I still managed to get
a huge performance gain from the native Access.
3) Access does not like functions as tables such as select * from
f_name(p1,p2...) it says its a bad from statement when you try to sort. I
solved this by writing a little workaround where my function first puts the
parameters in a table with the functionname and IP Address of the client.
Then on the server I have another function that reads the parameters from
the table on gets the data using a "for row in Execute..." statement.
Finally I have a view that calls the new function. That way access to a
function looks like a standard table.
4) I imported the citext case insensitive type, thank you Demolish for that.
I originally changed all my varchars and text fields to citext and changed
the ODBC setting to "Unknowns as LongVarChar", but that made them all memos,
which I thought was good. Then I found out you can't sort memos in Access.
So I changed all the Texts back to text and removed the ODBC setting, so the
unknown came in as an Access text (a DB varchar) and now the memos are not
case insensitive, but that's life.
5) One trick I learned is that Access does not refresh any ODBC properties
except for the server name when you Refresh Linked Tables and Choose a
different location. To change other settings you have to delete the linked
table and relink it.
6) Subforms lose their field linkage if the recordset is changed, even if
the fields in the new recordset are the same. If you check the properties it
will still say they are set, but they really aren't.
7) Subforms, I think it's actually only subdatasheets, can't have a
passthrough query as the recordsource, but views can be linked as tables, so
that is a good way of handling them.
That's all I can think of at the moment. Hopefully this information will
stop at least one person from pulling out all his hair as he tries to figure
out how this actually works.
Sim
