Access 2000 VBA/SQL - cannot create pseudo-index on linked SQL Server table 
Author Message
 Access 2000 VBA/SQL - cannot create pseudo-index on linked SQL Server table

Hi,

I am developping an Access application which requires a
linked SQL Server table. Rather than trust users to set up
the ODBC link themselves (!), the database checks for the
DNS on startup, and adds it if it is missing. The
connection string is as follows:

strConn = "ODBC;"
strConn = strConn & "DSN=" & rs("DSN") & ";"
strConn = strConn & "APP=Microsoft
Access;trusted_connection=yes"
strConn = strConn & "DATABASE=" & rs("DataBase") & ";"
strConn = strConn & "UID=" & rs("UID") & ";"
strConn = strConn & "PWD=" & rs("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName")

When the table is linked manually, the user is asked to
select the unique identifier, which creates a pseudo-index
on that field. I need to do this in code (otherwise my
update queries aren't "updateable"), however the following
code fails to acheive this:

db.Execute "CREATE UNIQUE INDEX MyIndex ON ODBCTable
('FieldName') WITH PRIMARY;"

It does not cause any errors, but does not create the
required pseudo-index, and subsequent update queries fail.

Any advice gratefully received!

Thanks,
Mark.



Tue, 15 Feb 2005 17:12:38 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. HELP!!..accessing access 2000 with linked SQL Server tables

2. Creating a Pseudo Index on a Sql Server View

3. Create Index using SQL in Access 2000 Table

4. Create Index using SQL in Access 2000 Table

5. Create Index using SQL in Access 2000 Table

6. DNS less Access Linked tables to SQL Server 2000

7. Linked Tables in SQL Server 2000

8. How to make a LINK Table in SQL 2000 Like MS Access

9. Updating A SQL server table through DAO and ODBC In Access 2000

10. Creating Tables in Access 2000 using SQL Text files

11. Creating link to SQL Server table without using DSN

12. ADOX.Append Creates a Read-Only Linked Table to SQL Server 7.0

 

 
Powered by phpBB® Forum Software