linking sql server table back end to access front end with code and no odbc dsn

[joe789]

Hi Folks,

In building a access front-end that uses linked SQL Server 2008 tables, for testing and development purpose I used integrated security with ODBC DSN and then linked the tables I needed to the front-end selecting primary keys on the tables that required it for data entry to be possible.

I am at the point where I would like to roll this application out, but I don't want to create ODBC DSN instances on the machines and don't want to delete and re-create the links to the tables with the appropriate username and password for each data entry user (different security for different users).

Is it easily possible to somehow, someway link these few SQL tables with appropriate primary keys without having to create a ODBC DSN and with the username and password actually stored in code SQL security NOT trusted/integrated security.

An example would be the following:

SQL Server IP address: 155.155.155.155
SQL Database: ELEPHANT
SQL Table: TIGER
Username: John
Password: Citizen

If there is an easy way to do this, I would really appreciate any help with this! Would something like a DoCmd.TransferDatabase acLink work? I suppose depending on the method, I may need to drop and then re-create the link each time, but that shouldn't be a problem. A function I can load in a Autoexec would rock if possible. Some of the tables require primary keys, which I haven't a clue how to create in code.

Thank you very much for any help you can provide!!

Joe

 

[bparkinson]

What you want is a DSN-less connection. Here's an MSDN article. There are plenty of other articles out there on this.

http://support.microsoft.com/kb/892490

 

[joe789]

Thank you ... I did get the code to work, but now I have another issue. I am essentially dsn-lessly linking three tables, two do not have primary keys and no manipulation is allowed by the user; however, one of the tables does have primary keys and user manipulation is required. When manually linking SQL tables to MS Access, I am given a choice as to what primary keys I want to use on that particular tables which would allow the user to add/delete/modify records. With this dsnless code, I am unsure how I would force the primary keys I need on the table I need them on.

I put the code in the article ID 892490 into a module and call that module in an autoexec:

AttachDSNLessTable ("TABLE1", "TABLE1", "555.55.555.555", "ABC", "USER", "PASSWORD")

The above code works perfectly, but if I need to assign a primary key to field2, field7, field9, and field15 of "TABLE1" that is being dsn-lessly attached ... is there a way to do this?

Thank you very much for any help or assistance you can provide!!!

 

[joe789]

Folks,

I guess I just set the primary keys on the necessary fields on the SQL end and that way when the DSN-less code is run, it carries them over ... so problem fixed! woo hoo! Thanks! It still would be neat to know how to manually set stuff like this while using a dsn-less code thou.

Thanks,

Joe

 

[mdlueck]

I have no idea why linked tables would suddenly change the need for a key... (vaguely remember some gripe about bad table designs and not being able to update records without a key) However I use the following function to populate the ODBC connection string into all Liked Table Objects in my FE.

'This updates the ODBC connection settings to the remote SQLServer database
Public Function UpdateConnectionsBasedOnTypePassThrough(MSysObjectsConnectString As String) As Boolean
On Error GoTo Err_UpdateConnectionsBasedOnTypePassThrough

  Dim adoCat As New ADOX.Catalog
  Dim adoTbl As New ADOX.Table
  Dim daoDB As DAO.Database
  Dim daoQDF As QueryDef

  'Define attachment to FE database
  Set adoCat = New ADOX.Catalog
  Set adoCat.ActiveConnection = CurrentProject.Connection
  Set adoTbl.ParentCatalog = adoCat

  'Refresh Linked Tables
  For Each adoTbl In adoCat.Tables
    If adoTbl.Type = "PASS-THROUGH" Then
      adoTbl.Properties("Jet OLEDB:Link Provider String") = MSysObjectsConnectString
    End If
  Next

  'Good return code
  UpdateConnectionsBasedOnTypePassThrough = True

Exit_UpdateConnectionsBasedOnTypePassThrough:
  'Clean up the connection to the database
  Set adoTbl = Nothing
  Set adoCat = Nothing

  Exit Function

Err_UpdateConnectionsBasedOnTypePassThrough:
  Call errorhandler_MsgBox("Class: clsObjMSysObjectsTbl, Function: UpdateConnectionsBasedOnTypePassThrough()")
  UpdateConnectionsBasedOnTypePassThrough = False
  Resume Exit_UpdateConnectionsBasedOnTypePassThrough

End Function