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 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
