Access-SQL Problem 4

[gmarrufo]

Greetings all,

My company has an application in Access 2000 (front-end/back-end) but they decided to move the data into MS-SQL 7.0 (back-end, leaving the front-end in Access) the original programmer of the application made the changes but they don't seem to work fine, here is a brief description of the tables layout.

- (1 "mod" table (primary key, "ID" autonumber))
- (multiple tables related to the unique "ID" of mod table, one-to-many relationship)

relationships seem to work fine in the original access back-end but looks like there are none on SQL, furthermore you can open all tables that have existing data and modify even erase data but if you append new information, a record-lock situation occurs. I'm new to Access/SQL, I'm more into networking than programming, any help would be greatly appreciated.

thanks,

G

 

[BSman]

I've moved two Access 97/2000 applications to Access/SQL Server 7 versions and am in the process of moving a third application over. One of these apps has a database with three tables that contain over 2 million records each and a database size of about 4 gig of data...and it works really well and really fast. I've learned some important things:

1. For any table that does not have a primary key, set one up to be used internally using an identify column (comparable to Access autonumber column). That makes the link easier, faster, and better and avoids trying to define a unique set of columns for the link (which Access will prompt you for).

2. DTS will import autonumber columns with the proper values, but does not make them identify columns automatically. If the autonumber value is meaningful to users (i.e., not just used internally to link lookup table values) and you want to continue with higher numbers, you need to make those columns identify columns, primary keys and set the starting value to one over the highest current value. For other tables where the ID value is just used internally, you can leave the starting value as 1 and SQL Server will assign the next unused value. So if the highest number is 2500 but 18 and 395 were not used, 18 will be assigned to the next new row and 395 to the second new row.

3. Any queries that are used in your front end in a way where the data should be updated and new rows added (like a query on one table that provides sorting for a form) must include the (new) ID column set up in SQL server, since that is the real link between the Access front end and the SQL back end.

4. For handling large amounts of data for reports, etc., any views and/or stored procedures you can set up in the SQL Server back end to reduce the amount of data to be sent to the Access front end will speed things up. This can be especially useful when grouping queries and queries combining data from multiple tables are involved. Even if you do sorting and selecting with an Access query that uses the SQL view as a source, if less data is sent over to Access or less work must be done by the Access front end (like with combining multiple tables), you should see a speed up of the results.

5. Use Access Pass Through Queries as another way to speed things up. These actually are Transact SQL query statements that you are sending the the SQL Server to be executed at the SQL Server and only the results returned. Unfortunately, in order to pass specific selection data for a SQL statement or to a stored procedure, you must create a new pass through query each time that includes the specific values needed at that time.

6. Remember that one critical difference in syntax between the Access SQL and SQL Server SQL is that date values in SQL Server are surrounded by single quotes (') rather than the pound sign as in Access (#).

7. You will have to define the relationships in SQL Server. I don't think that SQL Server's DTS imports the Access relationships.

8. Any text (254/255 character size) and memo fields that are automatically created by DTS as ntext fields in SQL Server must be redefined as text fields (they will probably have a size of "16", which does not seem to mean 16 characters), rather than nvarchar (as the 254 and 255 fields become) or ntext (which the memo fields become). Otherwise, any record with 255 or so characters of actual data will be unreadable in Access and any memo field will be unreadable because the ntext SQL type is not recognized by Access. But the text type of size "16" is (like a memo field). I suspect that nvarchar sends one or two extra characters back to Access, so if you have 255 characters of actual data SQL sends 256 or so characters to Access....and Access chokes (and makes that entire record unreadable) because it can only handle up to 255 text character fields.

Good luck. The results are worth it, and the transformation can actually work fairly easily.

One other observation. I looked into using Access Project, but found some concerns:

1. You need to modify the programming VBA code.

2. No tables are available in the front end application (for application control) because there is no Access database at all.

3. Although in Access 2000 Project you can create and modify the SQL Server objects (views, stored procedures, etc.), it's very good to know that you can't do that in the newer versions of Access Project. So if you need to create a view or stored procedure based on selection values of the moment you are screwed whenever you move up to a newer version, which of course will happen at some time.

 

[gmarrufo]

BSMan,

thank you for your extensive and well explained information, however I have a couple of questions I hope you may be able to answer.

1. I did set up a uniqueidentifier column in my tables that don't have a unique key, still at the time that I try to link the tables into Access, it asks me for setting up a Unique ID, If I don't I won't be able to update or delete the records (just add). And If I do set up a unique ID then at the time I add records, all of them end up with the same information in all columns. How do I go about fixing this?

2. Do I have to create an independent column to handle the one-to-many relationships between my main table and the sub-tables?

thanks again for your help.

G

 

[BSman]

1. The unique ID column must also be set up in SQL Server as the primary key, otherwise Access will ask you to set up a unique ID combination of columns. This seems to eliminate the Access request to define a unique ID combination.

2. You should not have to create any additional columns for relationships.


One other thing I forgot to mention: The DTS sets all true/false (bit) columns to not allow nulls. You should change the tables definitions in SQL server so all bit columns allow nulls. This is because a false in Access is represented by both a 0 and a null in a true/false column.

 

[jlitondo]

BSman, you have provided some great insight into the migration. I'm currently in the prosses of migrating a farely large access database to sql 2k with a Visual Basic 6 front end. So far my assumption with the front end part is that I could simply cut and paste the vba code from access forms into the vb forms and then do a little modification to make it work. Are their any import wizards in vb to import the forms and code modules?

 

[BSman]

I haven't tried to convert from Access to VB, so I don't know if there are any wizards. There may be some 3rd party tools available.

If you must migrate to VB, I would urge you to migrate to VB.Net, only because it's the newer version of VB and, from what I understand, has some significant differences from VB6. Otherwise you are going to need to do another conversion in the very near future and can expect to find that it will be more complex than you would expect. It might be better to move to an Access front end to your SQL back end for now and after that's successful, investigate getting the latest version of Visual Basic and converting to it.

 

[PHV]

So far my assumption with the front end part is that I could simply cut and paste the vba code from access forms into the vb forms and then do a little modification to make it work.

Don't assume that at all as access forms and vb forms are very different animals.
And what about the existing reports ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jlitondo]

PHV, the forms do not differ that much except when it comes to data access, subforms and the kinds of properties supported and since the language driving both kinds of forms is the same anyway, some level of cut and paste should be feasible.
I am moving the current reports to SQL Reporting Services. I've played around with this and it is a really neat tool, in my opinion, much better than crystal reports.

 

[PHV]

except when it comes to data access, subforms
Isn't the majority ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jlitondo]

No, in this application the majority is data manipulation. Don't post dumb responses to these forums, this is a serious website (the best) with serious members in it.

 

[LittleSmudge]

I'm with PHV on this.

I've tried to convert Access forms to VB6 for an app and found Sooooo many things that access does easily and simply that VB6 just can't do
( Just can't do straight out of the box - that is. It would have involved custom designs of data aware combo boxes etc as just one example )


As for BSMan's original post - I have a contribution for item 5

I use large numbers of PassThrough queries as I'm doing a lot of Access 2k front ends to MySL back ends. However I get round needed to define specific Passthrough queries to the various selection criteria that I need by doing the following :-

I define a query called qryPTvDemo like this

"SELECT * FROM tblDemo WHERE -1"

Then, when I need to filter the contents I have a general procedure defined

Public Sub ParamToPT(strQueryName As String, strClause As String)
Dim strSQL As String
Dim intPosn As Integer
   
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(strQueryName)

strSQL = qd.SQL
' in case the existing version has been saved with a Where Clause
' Strip the Where clause off the end
intPosn = InStr(strSQL, "WHERE")
If intPosn > 0 Then
    strSQL = Left(strSQL, intPosn - 1)
End If
' Now add the new Where clause
strSQL = Trim(strSQL) & " WHERE " & strClause
qd.SQL = strSQL

Set qd = Nothing
Set db = Nothing

End Sub

This then allows me to edit the Pass Through queries at run time and get all of the benefit of the client server structure to boot.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BSman]

Actually, I've been using a "query builder" form that I designed a number of years ago that enables the user to select a table or query, then select columns and criteria, then the sorting, and then either produces two open queries (one with details, one a grouping query based on the sort columns) as well as the where and order by strings to pass along to a form to filter the form's data display (or, for that matter, for use with a report). Originally it worked in Access with the Access back end database. About two years ago I created a version of the same form that creates a SQL compliant statement and a pass through query for a SQL Server back end. I just delete the current query (I use a standard name) and re-define the query with the same name. This way I can include criteria, order by, etc., in the SQL statement or call a stored procedure with the correct values for the variables to pass to the stored procedure.

I did learn that if you try to change the columns returned by a query without re-creating the query, Access will expect to see the same columns of data returned as originally existed with the first version of the query. This was driven home to me when I created a stored procedure that used a SQL statement stored in a table (in order to provide the custom queries from my form) and returned the results to Access. The stored query was executed through an unchanged pass through query after the SQL statement was updated in the table. Unfortunately, if there were 10 columns returned by the stored procedure originally and the new SQL statement returned 5 columns, 3 of which were different, Access still showed 10 columns with the original names. So I decided that deleting and creating a new query of the same name worked much better.