Automatically Update Link

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hainamle - 25 Jul 2005 03:43 GMT

Dear all,

I am creating a VBA code that link a table from SQL to my Access file.
However, I am just a beginner so that if I want to update link whenever I
open Access file I have to run the VBA code.

Could any one help me to create a VBA code that whenever I open my Access
file it is automatically update link or run my VBA code.

Thank you very much!

C

Signature

Nam

Reply to this Message

Ian B - 25 Jul 2005 05:19 GMT

Hi Nam

Code snip from working system may help.
Table is deleted and reattached which will make visible any changes in
SQL2000 table structure, otherwise any changes made to backend tables will
not be visble in Access
You will need to setup your own connection string (cS)

Need to pass in Table name and alias thus
Call RecreateConnection("tblLogData", "tblLogData")

Cheers

Ian B
~~~~~~~~~~~~~~~~~~~~~~
Private Sub RecreateConnection(sTableName As String, sTableAlias As String)

On Error GoTo RecreateConnection_Err
100: Call Initialise
110: Dim cS As String
120: Dim dbs As Database
130: Dim tdf As TableDef
140: On Error Resume Next ' IN CASE TABLE IS UNLINKED
150: DoCmd.DeleteObject acTable, sTableAlias
160: On Error GoTo RecreateConnection_Err
170: cS = "ODBC;driver={SQL Server};server=" & sSQLServer & ";database=" &
sSQLDBase & ";Trusted_Connection=Yes;TABLE= " & sTableName & "'"
190: Set dbs = CurrentDb
200: Set tdf = dbs.CreateTableDef(sTableAlias)
210: tdf.Connect = cS
220: tdf.SourceTableName = sTableName
230: dbs.TableDefs.Append tdf
240: dbs.TableDefs.Refresh
250: dbs.Close
RecreateConnection_Exit:
Exit Sub

RecreateConnection_Err:
Dim strErrString As String
strErrString = "Error Information..." & vbCrLf
strErrString = strErrString & "Error#: " & Err.Number & vbCrLf
strErrString = strErrString & "Line#: " & Erl() & vbCrLf
strErrString = strErrString & "Error Description: " & vbCrLf &
Err.Description
MsgBox strErrString, vbCritical + vbOKOnly, "Function:
RecreateConnection"
Resume RecreateConnection_Exit
End Sub

> Dear all,
>
[quoted text clipped - 10 lines]
> --
> Nam

Reply to this Message

hainamle - 26 Jul 2005 02:21 GMT

Dear Ian B,

Thank you for your answer & your VBA Code.

I still don't know how to do with your code.

I have already the same code as your in Access Module, but my problem is
that I don't know how to make it run when I open my Access file.

Could you explain more details what I should do to run the VBA code when I
open Access file.

Thank you very much.

Nam

Signature

Nam

> Hi Nam
>
[quoted text clipped - 59 lines]
> > --
> > Nam

Reply to this Message

Ian B - 26 Jul 2005 05:07 GMT

Hi Nam

Create a function called Startup (or any name you like)
This function should contain a line for all the RecreateConnections you need
to do
Public Function Startup()
Call RecreateConnection("tblLogData", "tblLogData")
....
....
End Function
You need to create a macro and name it AutoExec.
This macro will run anytime Access is started unless disabled, usually by
holding down the shift when starting Access
In this macro put the command "RunCode" as action
At the bottom of the screen under function name, type Startup()
Or as a check, browse for the function you created.

If in doubt, check help for "AutoExec" this has a better explanation than I
have given!

Good luck

Ian B

> Dear Ian B,
>
[quoted text clipped - 78 lines]
> > > --
> > > Nam

Reply to this Message