[AccessD] OLEDB connection string - Help please

You are correct, you won't need an ODBC data source definition on each pc.

Don't worry about the WorkStation ID, it is not needed.  Just don't put it in the connection 
when  linking through code.

Just for illustration, here's some code which lete you switch Back Ends easily between a local 
Access BE and  networked Access and SQL Server BEs:   It relies on all linked tables having 
a standard prefix  of "tbl"


Const strBEName = "NECDecision_BE.mdb"
Const strServerBEDir = "\\DotSQL\DataApps"
Const strSQLConnect = "ODBC;DRIVER=SQL 
Server;SERVER=DOTSQL;DATABASE=DoTPolicy;Trusted_Connection=Yes"
  
Function ConnectSQL() As Long
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, strSQLConnect, False
    End If
Next
ConnectSQL = True
End Function

Function ConnectBELocal() As Boolean
Dim tdf As TableDef
If Dir$(CurrentProject.Path & "\" & strBENAme") < " " Then
     MsgBox "Data file " & strBEName & " missing! It must be in the same directory as this 
application file.", vbCritical, "ConnectBELocal Failed!"
     ConnectBELocal = False
     Exit Function
 End If
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, CurrentProject.Path & "\" & strBEName, True
    End If
Next
ConnectBELocal = True
End Function

Function ConnectServerBE() As Long
Dim tdf As TableDef
If Dir$(strServerBEDir & "\" & strBEName) < " " Then
     MsgBox "Data file " & strBEName & " missing! It must be in the " & strServerBEDir & ".", 
vbCritical, "ConnectBELocal Failed!"
     ConnectServerBE = False
     Exit Function
 End If
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, strServerBEDir & "\" & strBEName, True
    End If
Next
ConnectServerBE = True
End Function

Function renewlink(tablename As String, datafile As String, AccessDb As Boolean) As Long
On Error Resume Next
DoCmd.DeleteObject acTable, tablename
On Error GoTo 0
Select Case AccessDb
Case True
DoCmd.TransferDatabase acLink, "Microsoft Access", datafile, acTable, tablename, 
tablename, False
Case False
DoCmd.TransferDatabase acLink, "ODBC Database", datafile, acTable, tablename, 
tablename, False
End Select
End Function
 

-- 
Stuart


On 4 Jun 2012 at 17:02, Andy Lacey wrote:

> Stuart, I could kiss you (it's ok I won't). That's it. Hallelujah.
> 
> So, I've still failed to get OLEDB to work but I think I have an ODBC
> connection which does not require an ODBC Data Source defining on the pc. Is
> that right? One thing, if I open the table in design view and look at the
> connection string it has a WSID parameter with my pc's name in it. Is that
> going to bite me when this is distributed?
> 
> Andy 
> 
> 

--
Stuart McLachlan

Ph:    +675 340 4392 
Mob: +675 7100 2028
Web: http://www.lexacorp.com.pg