Bringing Access app to Server - questions

[lorirobn]

Hello,

I'm not sure if this is the correct forum, but I have questions regarding bringing updates over to Access application and tables (front end and back end) on an SQL Server (also posted question on SQL server programming forum).

I have created an Access application for my client, on my laptop. (I am a programmer but this is my first Access application). I have no experience with Servers, except as a user. The I/T person copied the application over to the server last week. I watched, and have been trying to read up on it (huge Access 2000 Developer's Handbook, vol 2) to gain understanding.

This is what I have done, following the I/T person's suggestions:
I split my Access app. into 2 parts: tables, for back-end that will reside on the server, and objects, for front-end that will reside on individual computers. I set my application to use 'linked tables' to the data. I.T. copied back end onto the server. He put the Access application on the server just for a backup, and put it on one workstation, to start with. Set up ODBC to connect to the Server. Server is MS SQL Server, Standard Edition. My database is mdb. Application is Access 2000.

My questions are about making updates. I am still working on the Access application (at home on my laptop), and will be probably for the next month. So far, I brought over my changes once, and it was a major ordeal. I.T. person had me do this: he said if I pasted the new Access mdb over the existing one, I would have to re-link to the tables. To avoid this, he had me open the original/existing Access database on the one workstation. I imported the objects from the new/updated Access application from my laptop. But instead of overlaying objects, it imported ALL of them (with a '1' appended to the name). I had to delete all the original objects - forms, queries, reports - and then rename the new ones to eliminate the '1'. This was very time-consuming, and the potential for errors seemed too great. When done, I copied this new application up to the server, to have a copy there. My question is: how do other users go about updating an Access application on server/workstations? I was told that if I used the upsizing wizard instead, my vba code might not work.

A related question is, when I have to make changes to the tables (which are still work-in-process, to some degree), we have to relink to the tables. I have watched I/T person individually put in the indexes for each of my Access tables. Isn't there a way to keep the indexes I created in Access. Again, my concern is potential for errors/mistakes.

I also want to ask about adp versus mdb files. The Developer's Handbook I am reading refers mostly to ADP projects on SQL server, and this has gotten me concerned - am I incorrect to have mdb database instead? Do I need to convert to adp?

In case it matters, I created my application in Access 2003, and our first test workstation has Access 2003, but since other users will have Access 2000, I had to convert my application to Access 2000 before splitting.

I hope this all makes sense!
Thanks so much,
Lori

 

[PCLewis]

I'm not going to try and answer your questions point by point because there is a lot in your post that is already answered (I think) in various FAQ's on these boards.

The main perception that I think you have only partially taken up by what you have described is the definition of front end and back end. The point of separating the data from the "GUI" code is obvious and you have seen this yourself I guess. I have no idea why your IT person got you to import all of the objects into another database however. The whole point of separating the code from the data is so that you never have to do crap like that again.

If the tables have been migrated to MS SQL then at worst, you have to write some code to allow you to re-link your front end to the MS-SQL tables or to your local tables. This is my preferred approach. Another approach is for you to install MSDE on your laptop. (look MSDE up in google).

To write some code to allow you to relink to the correct database, consider the following three useful pieces of coding info:
1) The doCmd.TransferDatabase instruction allows you to link to a remote table programatically
2) The doCmd.DeleteObject command WILL delete a link (but be careful - it will also delete a table with data in it if you tell it to!)
3) The hidden table in All Access databases called MSysObjects contains a record for each linked table (and other very important stuff) in your database.

So programatically, you can relink by querying that table for the correct objects, then delete the link and re-add it with the DoCmd instructions.

If the data tables have changed in structure then you might need to establish a rigid process for documenting your changes. As for updating data, you should have a unique record identifier for each record in a table. So merging would be another thing quite easy to do in code. After making sure the table structure is correct, rrun an insert query to insert missing rows into the destination.

I don't think you should do it manually more than the second time. My rule of thumb is that on the third strike (of a repetitive process) I'll write code to do it instead.

I've had no experience with adp files so I can't comment on that. From the sound of your project though, it might not be necessary to go that way at all.

 

[lorirobn]

Thank you so much for your response. I knew there had to be an automated way of doing updates.

I next will look up these docmd's to relink, as well try some more for FAQ's that may answer my questions. (I did look, but didn't find appropriate ones for my q's). Meanwhile, if anyone could point me in the right direction, I'd greatly appreciate it.

 

[lorirobn]

Hi PCLewis,

Now that I have had a chance to think about your response, would love to clarify a few things, to make sure I've got it right.

With your preferred approach, of writing code to re-link front end to MS-SQL tables, would the procedure be as follows: copy the front-end mdb database from my laptop to the workstation, replacing the existing .mdb, and then re-link this new front-end to the MS-SQL tables via code?

Would you be able to explain what the option of having MSDE on my laptop would do for me?

The IT person had me do things a round-about way, including importing all the objects again, simply to avoid writing code to re-link. But I agree with you - if I'm going to do something repetitively, I'd prefer to have it automated. He also had mentioned MSDE to me, which is why I ask how it would help me.

As for updating data, I do have unique record identifiers for each record. But I've got a question about your statement: "After making sure the table structure is correct, run an insert query to insert missing rows into the destination. " Where would the modified table reside for me to compare it against the original table? From my laptop, would I would bring it to the Server under a different name, then compare it to the original? Or make it a local table? Or perhaps you mean something else?

Thanks; your suggestions are very helpful, and I just need some clarity so that I can fully understand and get it right.

Lori

 

[cmmrfrds]

Here is some code that can be pasted into the standard module to connect to sql server and find the tables that should be linked and then link them. This goes against Northwind, so if you have northwind installed on the server you can test this code.


Public Function AddLinkedTablesLooper()
Dim sConnString As String
Dim rs1 As New ADODB.Recordset
Dim tabName As String

' Create a new Table object
Set rs1 = GetSqlServerTables
rs1.MoveFirst
tabName = rs1!Name

While Not rs1.EOF
Call AddSQLServerLinkedTables(tabName)
rs1.MoveNext
If Not rs1.EOF Then
tabName = rs1!Name
End If
Wend

Set rs1 = Nothing

End Function

Public Function GetSqlServerTables() As ADODB.Recordset
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
Dim Myarr(50) As String, indx As Integer, maxindx As Integer
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=localhost;" & _
"Persist Security Info=False"

''Set cn = CurrentProject.Connection
cn.ConnectionString = connString
cn.Open
sql1 = "select name from dbo.sysobjects where xtype = 'U'"

rs.Open sql1, cn, adOpenStatic, adLockReadOnly

If rs.EOF And rs.BOF Then
MsgBox "No sql server tables returned"
Set rs = Nothing
Exit Function
End If

rs.MoveFirst
indx = 0
While Not rs.EOF
Myarr(indx) = rs!Name
indx = indx + 1
rs.MoveNext
Wend
maxindx = indx - 1
For indx = 0 To maxindx
Debug.Print "table name = "; Myarr(indx)
Next
Set GetSqlServerTables = rs
Set rs = Nothing
End Function

Public Function AddSQLServerLinkedTables(tabName As String)
Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim sConnString As String
On Error GoTo Errhandler
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={localhost};" & _
"Database=Northwind;" & _
"Uid=sa;" & _
"Pwd=;"

' Create and open an ADOX connection to Access database
Set oCat = New ADOX.Catalog
oCat.ActiveConnection = CurrentProject.Connection

' Create a new Table object
Set oTable = New ADOX.Table

Set oTable.ParentCatalog = oCat

With oTable
Debug.Print "loop = "; tabName
.Name = "NW_" & tabName
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Remote Table Name") = tabName
.Properties("Jet OLEDB:Link Provider String") = sConnString
End With
' Add Table object to database

oCat.Tables.Append oTable
oCat.Tables.Refresh

Set oCat = Nothing
Set oTable = Nothing
Exit Function

Errhandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description & vbCrLf
For Each er In CurrentProject.Connection.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Debug.Print "connection state = "; CurrentProject.Connection.state
Next

End Function

 

[lorirobn]

Thank you for the code, cmmrfrds.
But to be honest, I feel so 'unknowledgable' in this area, that I am not even at the point to put in such code. I wouldn't know what variables to change for my application. And I wouldn't know how to test such a function on my laptop, without SQL Server. (and what is the 'standard module' - the first form that opens in the application, such as the Switchboard?).

I will have to figure something out shortly, as I have changes to make at my client's site and wasted too many hours today trying to get it done in a roundabout manner, to no avail (when I was done, database was not updatable).

Thanks...
Lori

 

[cmmrfrds]

When you are in the database design window the bottom object under Macros is Modules, that is where a standard module is created. In a standard module you can create functions that are used throughout the application. Also, this is the place to put Global or Public variables.

The 3 functions I show are driven by the first function which is the only one that needs to be referenced. It can be called from the AutoExec Macro which is run when the application starts up or called one time from a Macro that you run.

In AutoExec.
Enter RunCode in one of the left boxes and then enter this function "AddLinkedTablesLooper()" in the function area.

Also, while in the standard module under Tools check the references. There should be a reference to the ADO and ADOX libaries.
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- set Reference to ADO Library
'-- Microsoft ActiveX data objects 2.6 library

The only other variable that needs to be changed is the server the code references. In the example it uses Localhost which is an sql server or msde on the local PC. Change Localhost to your server name.

 

[RandyMyers]

I think you are all making this far too complicated. I perform these types of operation all the time. I have SQL Server installed on my laptop... Personal edition works just fine (the CD coems with the standard edition CD).

I have ODBC conenctions setup to link to the Main SQL Server and the link to the local SQL Server.

I simply make a copy of the front end that is connected to the local copy, where I have made my modifications and use the link table manager to re-connect the tables to the main SQL Server. I make sure that I use standard names for the ODBC, so that when I re-link I can simply distribute the updated front end to all end-users (or replace the shared applicaiton on the network server if the all run the same front end from the network). Since we have standard ODBC names it all works correct for the end user.

If there was need to modify table structure then these modifications would need to be put in place on the Main SQL Server before re-linking the tables to the ODBC for the Main SQL Server.

It really is a pretty straight forward process and there certainly is no reason for getting into all the coding to do this.

 

[cmmrfrds]

The main advantage of doing the connection through code is that it eliminates needing to setup an individual ODBC on each user PC. I find it easier to roll out the app this way.

 

[lorirobn]

First of all, thank you ALL for your valuable posts. I am still trying to understand everything I've read and figure out what I need to do (my head hurts!).

Randy, your posting gives me hope that maybe this can be simple after all. I think a big missing link was that I didn't have anything like a server on my laptop. Now the IS person has installed a version of MS SQL Server on my laptop (he is fairly new to SQL Server, too, but knows more than I do). Now I have Enterprise Manager, Client Network Utility, Import and Export Data, Profiler, Query Analyzer, and Books Online as options under Microsoft SQL Server on my laptop. Not sure if this is Personal Edition, but I suspect not. Does this sound like what I can use?

So let me make sure I understand the steps you suggest.
-- I need ODBC connections setup to link to my client's SQL Server ("Main SQL Server") and also to link to a server on my laptop ("local SQL Server"). Is this correct? I have no idea how to setup ODBC connections on my laptop - any help on this? I did watch him do this on Main SQL Server, though.

-- I have my latest changes on a Front End .mdb on my laptop. Is this the "front end that is connected to the local copy" that you referred to? I need to make a copy of this, and re-link it to client's SQL Server? This may be really simple, but - how do I do that??? When using Linked Table Manager, do I have to change the location, from my local server to client's Main server? (didn't see that option in Linked Table Manager - just select/deselect checkmarks). Maybe that will become clear when I become familiar with ODBC, but yesterday, I knew my Front End was pointing to the wrong Back End. Also, what do you mean by 'standard names' in ODBC?

I am sorry for all these questions - this is very complicated for a SQL Server newbie, but I appreciate all this support and patience! Understanding is kicking in, albeit verrrry slowly!

Lori

 

[lorirobn]

Oh, I see, cmmrfrds... I didn't realize that was the advantage of having code. OK, so if I set up code today at home, where my front end and back end are both on my laptop, is there any way for me to TEST the code, without going to client's site and testing it on server?

Thanks!
Lori

 

[BSman]

This sounds more complicated than necessary to me. You do need to have the SQL Server database on your laptop and have an ODBC connection defined to it with the same SQL userid and password as you will use for clients. Every user machine will have an ODBC connection defined using the same database name and SQL userid and password as you are using on your laptop.

Most frequently you will probably not be changing the SQL Server database. If you do, use DTS to transfer changes (if they are extensive) to the production database or, if minor changes or a new view or stored procedure, you might just manually copy them.

No re-linking will be needed for a new version of your application, since you are using the same ODBC connection name, userid, and password for the links as are used in production. Just copy your updated application over the production version on the server.

If you change a SQL table (or view), you will have to relink that table on your version so Access will recognize the new layout of the table. I prefer to just define a new link, then in Access delete the old table link and delete the 1 from the end of the new link, assuming you named your tables starting with "dbo_", since Access automatically puts that on the front of the table names. This link and rename is extremely quick and easy to do and only needs to be done when the table is changed.

Bob S.

 

[cmmrfrds]

Hopefully, Northwind was installed on your copy of SQL Server. You can reference the copy you have with either the instance Name the SQL Server was installed as - probably your PC name, or LocalHost which will also reference your sql server. The code I pasted in will probably run as is if Northwind is on the server.

 

[lorirobn]

Hmmm... it turns out I don't think I have SQL Server on my laptop after all. All I see is SQL Server Enterprise Manager, and Client Network Utility. I thought one of these would be SQL Server, but it doesn't appear to be. (IS person installed it so I could make changes to SQL Server tables).

Am not sure how to proceed????

Is this where MSDE would come in?

My sole goal at this point is to update the .mdb application at my client's workstation. Can I write the above suggested code to re-link tables without having a server on my laptop?

your continued HELP is greatly appreciated.

Lori

 

[cmmrfrds]

Do you see an SQL Server when you use either Query Analyzer or Enterprise Manager?

 

[lorirobn]

I do not see anything that is obvious as an SQL Server within either, unless I am not looking correctly.

SQL Query Analyzer gives a little window saying 'Connect to SQL Server'. The drop-down list of servers is blank. It has 'connect using windows authentication' or 'SQL Server Authentication' options.

The Enterprise Manager has, on the left pane, a Console Root folder, and within that, Microsoft SQL Servers, and then SQL Server Group within that. 'No Items' is listed within it. However, yesterday while at my client's, and connected to their network, I remember seeing their workgroup within this.

Thanks,
Lori

 

[cmmrfrds]

Install MSDE on your PC so you have something to test with. I believe it is on the Office CD.

http://www.vb123.com/toolshed/99_accvb/installing_msde.htm

 

[lorirobn]

OK, I installed MSDE from my Office CD. MSSQLServer is now running. Thank goodness for small miracles.

Now, do I somehow have to put my back end tables onto this server? (if so, how?)
And then create the standard module using cmmrfrd's code that's listed above?

How do I know the instance name?
How do I know if Northwinds is installed on my SQL Server?

Thanks,
Lori, slowly step-by-step getting 'somewhere'

 

[cmmrfrds]

The MSDE probably uses the name of your PC as the server name. Also, you should be able to access with the name localhost. Go to enterprise manager and register this server under your group. Then you should be able to look through the server and see what databases are installed. Or, go to Query Analyzer and connect to one of the above.

 

[lorirobn]

Thanks to everyone who helped me - I FINALLY got it working yesterday, with IS person's help. I'm not even sure exactly what we did, but we got the back end tables onto my local server (MSDE), imported tables, had to do a bit of setting up permissions and roles and authorizations on SQL Server. Those of you who said linking the tables was easy were absolutely right - once we had things set up correctly, linking the tables in Linked Table Manager was a BREEZE (not having permissions set up correctly was one of the causes for my non-updating problems beforehand).
A huge THANK YOU to all who responded... I would never have been able to get as far as I did without your help. Cmmrfrds, thanks for sticking with me for as long as you did. I wouldn't have been able to install MSDE, and more, without your help.
Sincerely,
Lori