[AccessD] Connect Access app to MySQL using a connection string not a DSN

Would it be a little safer

1) proving that the table is a linked table before deleting it
2) testing whether linkage works (ie, datafile is a valid path) before
deleting a table which might be working
     But which the user got the original path wrong

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Saturday, March 09, 2013 5:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connect Access app to MySQL using a connection string
not a DSN

You can strip the following code down to a single
DoCmd.TransferDatabase......

I'll leave that as an exercise for the reader :-)


Const strMySQLConnect = "ODBC;.........

Function ConnectMySQL() As Long
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
    If Left$(tdf.Name, 3) = "tbl" Then
         renewlink tdf.Name, strMySQLConnect, False
    End If
Next
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 9 Mar 2013 at 15:36, Arthur Fuller wrote:

> I have the string itself. I'm not sure how to use it. New connection 
> object and assign the string to it? Normally I've used DSNs to 
> connect, but I need to learn how to do it DSN-lessly.
> 
> TIA
> 
> --
> Arthur
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com