Обсуждение: dropped columns and ms access
psqlODBC 07.03.02 PostgreSQL 7.4.3 (Cygwin) MS Access 2003 Win XP Pro Is there any workaround for the psqlODBC problem of MS Access losing the ability to link to tables after dropping columns, other than pg_dump and then reload? Thanks, David P. Lurie
Hello,
I'm not aware of a problem like that, really. Personnally, after each change to the backend, I relink all tables,
programmatically.Here is the code:
-------------------------------------
Option Compare Database
Option Explicit
'*****************************************************************
' The DoesTblExist function validates the existence of a TableDef
' object in the current database. The result determines if an
' object should be appended or its Connect property refreshed.
'*****************************************************************
Function DoesTblExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function
'*****************************************************************
' This relinks everything
'*****************************************************************
Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String
Dim strConn As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim login As DAO.Recordset
Dim tbl As DAO.TableDef
Dim strDSN As String
' ---------------------------------------------
' We create the ODBC connection
' ---------------------------------------------
Set db = CurrentDb
Set login = db.OpenRecordset("select * from tblLogin")
DBEngine.RegisterDatabase global_dsn_name(), _
"PostgreSQL", _
True, _
"Description=SQL - " & login("DataBase") & _
Chr(13) & "Server=" & login("Server") & _
Chr(13) & "Database=" & login("DataBase") & _
Chr(13) & "Username=" & login("UID") & _
Chr(13) & "Password=" & login("PWD")
' ---------------------------------------------
' We create / refresh table links
' ---------------------------------------------
Set rs = db.OpenRecordset("select * from tblODBCDataSources")
While Not rs.EOF
strTblName = rs("LocalTableName")
strConn = "ODBC;"
strConn = strConn & "DSN=" & global_dsn_name() & ";"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=" & login("DataBase") & ";"
strConn = strConn & "UID=" & login("UID") & ";"
strConn = strConn & "PWD=" & login("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName")
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If
rs.MoveNext
Wend
CreateODBCLinkedTables = True
MsgBox "Links are ok. Please restart MS Access.", vbInformation
CreateODBCLinkedTables_End:
db.Close
Application.Quit
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.description, vbCritical, "MS Access"
Resume CreateODBCLinkedTables_End
End Function
-------------------------------------
I use it in conjunction with a local table called "tblODBCDataSources", made up of two columns:
- ODBCTableName text
- LocalTableName text (PK)
I also use another local table, called "tblLogin":
- UID text (PK)
- PWD text
- Database text
- Server text
I hope this will help you. After a relink, I restart MS Access, the only workaround I was able to find in order to have
permissionsthat work.
------------------
Philippe Lang
Attik System
-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de David P. Lurie
Envoyé : vendredi, 25. juin 2004 08:03
À : pgsql-odbc@postgresql.org
Objet : [ODBC] dropped columns and ms access
psqlODBC 07.03.02
PostgreSQL 7.4.3 (Cygwin)
MS Access 2003
Win XP Pro
Is there any workaround for the psqlODBC problem of MS Access losing the ability to link to tables after dropping
columns,other than pg_dump and then reload?
Thanks,
David P. Lurie
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
"Philippe Lang" <philippe.lang@attiksystem.ch> wrote in message news:6C0CF58A187DA5479245E0830AF84F42080336@poweredge.attiksystem.ch... >I'm not aware of a problem like that, really. Personnally, after each change to the backend, I relink all tables, programmatically. Here is the code: Thanks, I'm getting close to deploying an app at my office. Programmatically relinking will make deployment easier for my employees, without having everyone navigate menus and the Data Sources applet in Control Panel. My current problem isn't with the relinking process, but with creating new links after table structure modification. I got the dreaded #NAME? field contents odbc error after changing a few table structures, primarily deleting columns. No previous problems when columns were added. The cause was not apparent until I found some threads related to this issue, actually in this list, back in 12/2002 through 2/2003. My normal sequence of events after a table modification on the backend is to delete the previous link, then create a new link. The problems showed up after this sequence, with the error described above. The following post from 2/2003 addressed a fix, which seems to work thus far: a.. From: Michael Calabrese <m2calabr ( at ) yahoo ( dot ) com> b.. To: pgsql-odbc ( at ) postgresql ( dot ) org c.. Subject: Re: Error when accessing tables with deleted columns d.. Date: Mon, 3 Feb 2003 14:16:29 -0800 (PST) ---------------------------------------------------------------------------- ---- I just wanted people to know if you have deleted a column in postgres and can not relink the tables in MS Access, the current solution is to dump the database and reload it. I was using 7.2.5 version of the odbc driver and tested the CVS that Dave kindly arranged for me. If someone would like me to test a later CVS please just email me with the location of a compiled version and I will attempt to recreate my problem. Thanks for everyones help, Michael Thanks, David P. Lurie
Hello,
I have tested what you mention, and I have no problem with deleted columns, after refreshing the linked tables in MS
Access.(2000) They just disappear, if I manually refresh the links with the tools menu, or if i do it programmatically
withthe code I gave you in my previous mail.
I use the ODBC driver version 7.03.02.09 and 7.03.02.08, they both work fine. I use the "Postgresql" driver, not the
Legacyor Unicode one. Be careful with driver 7.03.02.00, I had some weird problems with it. And pardon me if I'm wrong,
butI think it's the version that the latest installer uses. I had to write a small installer that replaces the dll with
thelatest one.
Have you tried installing the latest MDAC from Microsoft? I personnally installed the version 2.8 on all the clients,
fromNT4 to XP. With this config, everything runs really fine.
Philippe
-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de David P. Lurie
Envoyé : vendredi, 25. juin 2004 17:11
À : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] dropped columns and ms access
"Philippe Lang" <philippe.lang@attiksystem.ch> wrote in message
news:6C0CF58A187DA5479245E0830AF84F42080336@poweredge.attiksystem.ch...
>I'm not aware of a problem like that, really. Personnally, after each
change to the backend, I relink all tables, programmatically. Here is the
code:
Thanks,
I'm getting close to deploying an app at my office. Programmatically relinking will make deployment easier for my
employees,without having everyone navigate menus and the Data Sources applet in Control Panel.
My current problem isn't with the relinking process, but with creating new links after table structure modification. I
gotthe dreaded #NAME? field contents odbc error after changing a few table structures, primarily deleting columns. No
previousproblems when columns were added. The cause was not apparent until I found some threads related to this issue,
actuallyin this list, back in 12/2002 through 2/2003.
My normal sequence of events after a table modification on the backend is to delete the previous link, then create a
newlink. The problems showed up after this sequence, with the error described above.
The following post from 2/2003 addressed a fix, which seems to work thus
far:
a.. From: Michael Calabrese <m2calabr ( at ) yahoo ( dot ) com>
b.. To: pgsql-odbc ( at ) postgresql ( dot ) org
c.. Subject: Re: Error when accessing tables with deleted columns
d.. Date: Mon, 3 Feb 2003 14:16:29 -0800 (PST)
----------------------------------------------------------------------------
----
I just wanted people to know if you have deleted a column in postgres and can not relink the tables in MS Access, the
currentsolution is to dump the database and reload it.
I was using 7.2.5 version of the odbc driver and tested the CVS that Dave kindly arranged for me. If someone would
likeme to test a later CVS please just email me with the location of a compiled version and I will attempt to recreate
myproblem.
Thanks for everyones help,
Michael
Thanks,
David P. Lurie
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
I'm currently using MDAC 2.7, and the ODBC driver is "Postgresql" 7.03.02.00. The same error now occurs intermittently without having made any changes to the backend, still only with tables that have had columns deleted at some point. The last episode still persisted after relinking, but cleared after closing, then reopening Access. 7.03.02.09 was in the snapshot folder on the postgresql ftp site. Will try changing the ODBC driver as the next step. Thanks for your assistance. "Philippe Lang" <philippe.lang@attiksystem.ch> wrote in message news:6C0CF58A187DA5479245E0830AF84F42080337@poweredge.attiksystem.ch... Hello, I have tested what you mention, and I have no problem with deleted columns, after refreshing the linked tables in MS Access. (2000) They just disappear, if I manually refresh the links with the tools menu, or if i do it programmatically with the code I gave you in my previous mail. I use the ODBC driver version 7.03.02.09 and 7.03.02.08, they both work fine. I use the "Postgresql" driver, not the Legacy or Unicode one. Be careful with driver 7.03.02.00, I had some weird problems with it. And pardon me if I'm wrong, but I think it's the version that the latest installer uses. I had to write a small installer that replaces the dll with the latest one. Have you tried installing the latest MDAC from Microsoft? I personnally installed the version 2.8 on all the clients, from NT4 to XP. With this config, everything runs really fine. Philippe
How do I install the snapshot; it has the new psqlodbc.dll file and a registry editor update file. I assume that the new file replaces the old dll in windows\system32, and that I run the registry updater next. The postgresql main web site and gborg are apparently down, or at least from my isp connection, so I couldn't get to the installation docs. David P. Lurie
Still having the same problems with 7.03.02.09 , but the deleted columns may
turn out to be unrelated.
I can load Access, and successfully run forms designed with linked tables,
or open the tables in datasheet mode without error.
The Access mdb containing the linked tables can be left loaded, I can come
back after a period of time, try and open the linked tables in question and
get the previous errors.
Relinking, deleting and recreating links, or closing and reopening the
Access mdb doesn't clear the error.
Closing and reloading Access clears the errors each and every time.
postmaster.log shows some errors that seem to appear once for each failed
attempt to link to a table:
ERROR: relation "msysconf" does not exist (This can be ignored)
ERROR: invalid input syntax for type bigint: ""
ERROR: invalid input syntax for type bigint: ""
ERROR: invalid input syntax for type bigint: ""
ERROR: invalid input syntax for type bigint: ""
ERROR: invalid input syntax for type bigint: ""
The space between the double quotes contains an up arrow when viewed from a
cygwin bash prompt, and a tiny square when viewed in MS Word.
Most of the tables have an int8 field that set up to function like an Access
autonumber or SQLServer ident field, using a sequence, with default value of
nextval('sequence_name'). Access doesn't have a native int8 data type,
although Access projects can use int8 (bigint for SQLServer) data types with
SQLServer tables.
The ODBC driver is set to use row versioning, and all timestamp fields are
timestamp(0) per recommendations for Access. The errors occur whether int8
is configured for default or numeric.
I'm going to change the int8 columns to int4 columns and see if the problem
still occurs.
David P. Lurie