Deploying Access db to different locations

[lupis]

Hi,



I am completing a access db. The local db has linked tables to a online MS SQL Server via a dsn. It works fine on my home network, but not anywhere else, so to be able to use the append and update queries to the linked tables what is the soloution to this.
Do I need to use a specific type of DSN, User, System or File DSN?

Thanks
Sohail

[Jim Doherty]

Your login to SQL server is presumably by user name and password I take it as opposed to windows network authentication therefore I assume you have some mechanism in your frontend to cater for that logging in process.

You could have a DSN-less reference in code to do the connection for you (this reduces the need to get others to create DSN,s on the machine, or indeed you could test for and create the DSN on their machine in your VBA module routines on startup) followed by a VBA loop through the TableDefs collection deleting and reattaching the relevant tables on startup.

One technique you might wish to think about is storing a table of table names that you wish to attach from the server. (You could prefix that table 'Usys' as tables prefixed with this automatically inherit hidden status) You could then loop down that table in VBA on start up and run the following function against each name found. Yes I know your tables are attached already and all they need is refreshing but what if one becomes unattached for whatever reason ie some savvy user messing things up

 
Function AttachODBCTable(strLocalTableName As String, strRemoteTableName As String, strServer As String, strDatabase As String, Optional strUsername As String, Optional strPassword As String)
On Error GoTo AttachODBCTable_Err
Dim td As TableDef
Dim strConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = strLocalTableName Then
CurrentDb.TableDefs.Delete strLocalTableName
End If
Next
If Len(strUsername) = 0 Then
'//Use trusted authentication if strUsername is not supplied.
strConnect = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";Trusted_Connection=Yes"
Else
'//This will save the username and the password with the linked table information.
strConnect = "ODBC;DRIVER=SQL Server;SERVER=" & strServer & ";DATABASE=" & strDatabase & ";UID=" & strUsername & ";PWD=" & strPassword
End If
Set td = CurrentDb.CreateTableDef(strLocalTableName, dbAttachSavePWD, strRemoteTableName, strConnect)
CurrentDb.TableDefs.Append td
AttachODBCTable = True
Exit Function
AttachODBCTable_Err:
AttachODBCTable = False
MsgBox "AttachODBCTable encountered an unexpected error: " & Err.Description, "System Message"
End Function

[lupis]

Hi Jim,

I am really a newby when it comes to VBA. So I will go stepwise through your reply.
Yes, I logon to the online SQL Server via my ISP with a username and password. When setteing up the linked tables in Access this info can be save as an option as opposed to supply it each time you access that table.

The two online tables are called dbo_Contacts and dbo_Actions_local. The table containg the names of these tables to be attached, what kind of structure should it have? Do they need a autoincremented PK and what datatype should the field containg the table names have?

To the VBA script then, so the function called AttachODBCTable has six arguments, but the argument strLocalTableName (there are two local tables, Contacts and Actions_local) and likewise strRemoteTableName(there are two remote tables called dbo_Contacts and dbo_Actions_local). Should there be a arguement for each table to be provided for this function?

Then there is a error handler.

Then we declare two variables: td As TableDef and strConnect As String (I guess the variabel td is for the collection Tabledef for the onlinetables dbo_Contacts and dbo_Actions_local I want to loop through to attach at startup?) and strConnect is for the connection string.

Then you loop through the tables in the TableDefs collection and then a if condition to check if these are the local tables Contacts and Actions_local, but I dont understand why I have to delete them.

Then there is the second if conditon if the arguement Username for Len is empty, if empty trusted authentication is used. Else the strConnect will have the connecstion string info.
(what would that strConnect look like with the following info:

• SERVER=sql04.active24.com
• DATABASE=DBXXXX
• UID=XXXXS
• PWD=xxxxx

Then we are setting the table collection variabel td with arguments for the local and online tables and the password, but isnt password in the arguement for strConnect also?

CurrentDb.TableDefs.Append td //tabledef is attached to the current db?
AttachODBCTable = True //Function call?

and then the error handler finally

I understand the basis, that I create a table that has the names of the remote tables I need to connect to on startup, these tablenames are provided as an agruemnt for the function to attatch connection string info and loop through. But provided with the connection info I mentioned earlier..and say if I have a table called UsysUpdate containing the names of the remote tables how would the function code look like.

Thanks
Sohail

[Jim Doherty]

Q) The two online tables are called dbo_Contacts and dbo_Actions_local. The table containg the names of these tables to be attached, what kind of structure should it have? Do they need a autoincremented PK and what datatype should the field containg the table names have?
　
A) Wow only two tables ...that is even easier then and no need for a table to store the table names you merely run the AttachODBCTables function for each table and not bother looping through a table of table names. But if you wanted to have this table you only need a single field of sufficient size to store the name of the table...doesnt need autoincrement just one text field would do


Q) To the VBA script then, so the function called AttachODBCTable has six arguments, but the argument strLocalTableName (there are two local tables, Contacts and Actions_local) and likewise strRemoteTableName(there are two remote tables called dbo_Contacts and dbo_Actions_local). Should there be a arguement for each table to be provided for this function?
　
A) Yes........when Access links a table from an ODBC database it aliases the table name resident on the server with a name locally. This name can be 'anything' actually when it is linked to Access as an attachment, you can even rename it in the access database window and it has no impact on the name held on the server........ Remember it is only a name that Access uses for referencing it 'locally'. Access adds the local name to its TableDefs Collection as a 'linked table' (as denoted by the DATABASE column which you will see if you look in the MySysObjects table the column called 'database') This is why the two arguments exist for strLocalTableName AND strRemoteTableName in other words "What is the name of the table on the server you want to connect too and what name would you like it to have in Access once attached"
　
Q) Then we declare two variables: td As TableDef and strConnect As String (I guess the variabel td is for the collection Tabledef for the onlinetables dbo_Contacts and dbo_Actions_local I want to loop through to attach at startup?) and strConnect is for the connection string.


A) Close!.....td is an object variable to refer to the table as it is 'locally' attached. Remember an attached table is appended to the collection of tables when it is linked by Access. It is logical and it has to do that in order to know that it has the table referenced as part of its structure. There nothing magical in this, it is merely Access saying unto itself "Huh I have a table and its name is this and oh yes! its an attached table and if I delete the link to it at any time I don,t care, it is resident on SQL Server I dont keep the data... only a pointer to that data" strConnect is indeed the DSN string it uses when establishing a connection

Q) Then you loop through the tables in the TableDefs collection and then a if condition to check if these are the local tables Contacts and Actions_local, but I dont understand why I have to delete them.

A) We are not deleting 'The table' remember it is a server table it is never actually in Access. We are merely deleting the link to it ...because we then relink it as part of the CurrentDb.CreateTableDef statement further on in the program flow. Dont be fooled by seeing a world globe icon in Access it is only ever there to represent a link. The table is ALWAYS on the server.

Q) what would that strConnect look like with the following info

A) You pretty much have it...each element is an argument to satisfy the connection

Q) Then we are setting the table collection variabel td with arguments for the local and online tables and the password, but isnt password in the arguement

A) Yes it is but strConnect is used in the createtabledef to set its reference to the source look at the msysobject table database column to see

Q) CurrentDb.TableDefs.Append td //tabledef is attached to the current db?

A) Yes

Q) AttachODBCTable = True //Function call?

A) AttachODBCTable= True means if it reached this point in the program flow the result of the program flow will be true to any other function that called it.

Q) I understand the basis, that I create a table that has the names of the remote tables I need to connect to on startup, these tablenames are provided as an agruemnt for the function to attatch connection string info and loop through. But provided with the connection info I mentioned earlier..and say if I have a table called UsysUpdate containing the names of the remote tables how would the function code look like.

Something similar to the following....(remember we are only giving the parameter variable 'MyTable' twice in the function call because we want the table to have the same name when linked locally as the table name on the server)

 
Function AttachTablesAtStartup()
Dim db as DAO.Database
Dim rs DAO.Recordset
Dim MyTable as String, MyDSN As String
Dim MyServer As String,MyServer as String, MyUID as String,MyPW as String
set db=CurrentDb
MyDSN=""
MyServer="sql04.active24.com" 
MyDatabase="DBXXXX"
MyUID="XXXXS"
MyPW="XXXXX"
set rs=db.OpenRecordset("SELECT Fieldname FROM UsysUpdate",dbOpenSnapshot)
Do While NOT rs.EOF
MyTable = rs!YourFieldName
x=AttachODBCTables(MyTable,MyTable,MyServer,MyUID,MyPW)
rs.MoveNext
Loop
rs.close
db.close
set rs=Nothing
set db=Nothing

I trust this helps you!!

[lupis]

Hi Jim,

Is DAO native to Access?

So you begin with declaring the database and recordset variables, then MyTable (Will the variable MyTable contain the references to fieldnames in the UsysUpdate?)

What does the variable MyDSN contain? The Data Source?
Why is set to an empty string? MyDSN=""

Then the connection string: Dim MyServer As String, MyServer as String, MyUID as String, MyPW as String
But why is Dim MyServer As String, MyServer as String declared twice? Should it be?
Dim MyTable As String, MyServer as String, MyUID as String, MyPW as String

set rs=db.OpenRecordset("SELECT Fieldname FROM UsysUpdate",dbOpenSnapshot)
To find out the table names in UsysUpdate? So if my SQL Server tables are called dbo_Contacts and dbo_Actions_local are these the remote tables to be stored in the UsysUpdate as fieldnames?

Then the loop until end of file through the recordset,
Can you explain this line of code: MyTable = rs!YourFieldName
Setting the tables to be referenced to the recordset of the FieldName in the UsysUpdate table??

x=AttachODBCTables(MyTable,MyTable,MyServer,MyUID, MyPW) but the variable x is not declared previously?

Then finally the cleanup.

Thanks for you patientice with me Jim, really appreciate it

Thanks
Sohail

[Jim Doherty]

Q) Is DAO native to Access?

A) Yes..DAO (Data Access Objects) forms part of JET (Joint Enterprise Technology) the underlying technology Access uses.

Q) So you begin with declaring the database and recordset variables, then MyTable (Will the variable MyTable contain the references to fieldnames in the UsysUpdate?)

A) Yes...this information is being passed to the function from textual data contained in rs!YourtableName in the Do While loop perfomed on the recordset.

Q) What does the variable MyDSN contain? The Data Source?
Why is set to an empty string? MyDSN=""

A) Remove that..my mistake whilst typing TV I obviously was not concentrating 100% you do not need it!! because we are breaking the DSN up into pieces by variables that follow it...MyServer, MYUID, MyPW. Also the Dim MyServer As String declared twice was me Ctrl+V pasting ham fistedly balancing a laptop on my knee. Remove that duplication.

Q) So if my SQL Server tables are called dbo_Contacts and dbo_Actions_local are these the remote tables to be stored in the UsysUpdate as fieldnames?

A) Not as field names Sohail, as data values ie: the names of your remote tables should be contained textually in the local table...... in your local table UsysUpdate you should only have two rows each row containing the name of the table you wish to relink from the server.

Q) Can you explain this line of code: MyTable = rs!YourFieldName
Setting the tables to be referenced to the recordset of the FieldName in the UsysUpdate table??

A) We are grabbing the 'textual' content of the field in the recordset and passing it to the MyTable variable so that it can be used in the AttachODBCTable function argument in the line that follows

Q) x=AttachODBCTables(MyTable,MyTable,MyServer,MyUID, MyPW) but the variable x is not declared previously?

A) You are quite right!! we have not declared the x variable. It is 'good' practice to declare all variables although we are not obliged to in each and every case unless the Option Explicit declaration is set at the top of your code module. Undeclared variables take the variant datatype value by default so the program will still run. The X variable is merely holding a value returned from the AttachODBCTable function once it has run (true or false) to denote failure or success. It is this return value that 'could' be used to do, or not do, something based on that value. We are not currently doing anything in this current flow with that (but you could do). Ordinarily you 'would' examine the return value of a function call and redirect on failure to an error handler line or some other 'tidying up' element of your program flow.

Remember..... I did say the program was 'Something similar to the below' and I am pleased you are picking holes in this because that shows me you are forensically scrutinising which I like personally :-)

Q) Then finally the cleanup.

A) Yes.... cleanup is necessary to de-allocate memory assigned to object variables at runtime. Again this is good practice. You will see many examples out there of posted code where in flow this is not done. Again, this could be simply down to the author balancing a laptop on his knee watching TV and not concentrating, alternatively it could be evidence of a lazy programming style.

Sohail...I have more patience than many but rather than turn the thread into an episode of 'War and Peace' maybe your acquisition of a technical book on Access would be a much better proposition for you. All I am really doing here is reiterating much of what exists already in a book already?!

Regards

[lupis]

Hi Jim,

So what should be the fieldname in the table UsysUpdate containing the two rows with the names of the reomote tables called dbo_Contacts and dbo_Actions_local? I just named the fieldname to RemoteTables.

Function AttachTablesAtStartup()
Dim db as DAO.Database
Dim rs DAO.Recordset
Dim MyTable as String, MyDSN As String
Dim MyServer as String, MyUID as String,MyPW as String
set db=CurrentDb

MyServer="sql04.active24.com" 
MyDatabase="DBXXXX"
MyUID="XXXXS"
MyPW="XXXXX"
set rs=db.OpenRecordset("SELECT Fieldname FROM UsysUpdate",dbOpenSnapshot)
Do While NOT rs.EOF
MyTable = rs!YourFieldName
x=AttachODBCTables(MyTable,MyTable,MyServer,MyUID,MyPW)
rs.MoveNext
Loop
rs.close
db.close
set rs=Nothing
set db=Nothing

Where in access should the code snippet be. Should it be as a macro, module or a Class moduel..Should it be a private or public function and should there be a End Function at the end? Is it possible to have code loaded at startup or better du have it connected to a button click action?

Thanks
Sohail

[Jim Doherty]

Check your private messages Sohail I sent you one.....

You can name the tables the same locally as remote. As for where to put the code... you can put it in a standard module and test it out using the immediate window. To get this up and running you then merely use an autoexec macro with a Runcode Action to call the function