| Author |
Topic |
|
ackweb
Yak Posting Veteran
54 Posts |
 Posted - 2003-05-26 : 16:33:56
|
| This is an MS Access problem, but also involves SQL Server as I'll briefly describe. I'm running a SQL Server application with data from multiple organizations in each table. In my SQL Server tables, each record is uniquely identified by the combination of it's OrgID and RecordID (which I sequence for each organization). I periodically download copies of this data into MS Access for a given organization. In this case, I query the SQL table using the OrgID and download the Results into the appropriate MS Access table with the RecordID in the corresponding AutoNumber field. All of this appears to work fine and all the data ends up in the appropriate fields in the MS Access table. If there are 300 records in the query result, then the last RecordID in my MS Access table is 300 (as it should be). The problem arises when I try to add another record in this MS Access table. You would expect, as I did, that the next RecordID would be 301. However, the next RecordID is an arbitrary number that is several times larger than 301 (e.g. 1152). Subsequent records added to MS Access are sequential (e.g. 1153), but there is an unexplained gap in the AutoNumber sequence from (in this example case) 300 to 1152. I'm currently using the "int" data type for this RecordID field in SQL Server. I spoke with an MS Access developer that I know and he said that I needed to be using only "long" integers for AutoNumber fields and thought that changing this data type would resolve the problem. Unfortunately, I've discovered that there is no "long" SQL Server datatype displayed in the Design Table view of Enterprise Manager. I tried "bigint", but this resulted in an even larger sequence gap. I'd appreciate any suggestions for resolving this problem. Thanks! |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-05-26 : 21:08:36
|
| hi ackwebsounds to me like Access is "remembering" the next id available in the original mdb file. Have you checked that? In which case, your option might be to recreate the table locally (eg by using a maketable query) and then change the property of the id field to autonumber. You can do all that in code if you need, let me know how you get on.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
 |
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-05-26 : 23:29:27
|
| rrb-I've always been replacing data in the MS Access with more corresponding records from SQL Server than were in the original table. I have explored whether there is any relationship between the last AutoNumber RecordID in the original MS Access database and the AutoNumber RecordID after the download. Specifically, I've tried the SQL Server download into a MS Access table with no prior AutoNumber RecordIDs. This download into a "fresh" table still resulted in a post-download RecordID that was several times larger than the last RecordID in the corresponding SQL record set. I'm trying to avoid having to create the table from scratch, but may need to do just that to solve this problem. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-05-27 : 00:44:14
|
| So when you say "SQL Server download" - how (exactly) are you getting the data from SQL Server to Access?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-05-27 : 10:07:33
|
| I have a DTS package which queries the SQL Server database for all records within a given table (e.g. tblExample) that have a particular OrgID. The results of this query are then used to replace the current contents of the corresponding tblExample in MS Access. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-05-27 : 13:35:55
|
| It looks to me like you are useing this to Isolate the "access" users from your main tables preventing data contamination. Might I suggest using SQL drop/make table in place of Your DTS package option (or as your DTS package)to create a SQL table for the Back end of access and then us a .adp development instead of .mdb for your front end. This will allow you to control your Auto number Sequence.JimUsers <> Logic |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-05-27 : 20:11:41
|
| I think that's what I said.Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-27 : 22:05:03
|
| I would just link to the SQL data from Access. Why copy the data when you can just grab it? Linked tables can be quite efficient in Access, even in Access 97.- Jeff |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-05-28 : 00:33:35
|
| Funny that you should mention Access 97. My client organizations are running their database application on Access 97, which explains why *.adp isn't an option. It sounds like linking the SQL and Access databases will be the way to go. I'd appreciate any suggestions for information and/or links on this subject. Thanks! |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-05-28 : 00:35:01
|
| Better off to just ask - what would you like to know?Cheers--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-05-29 : 13:08:41
|
| I've done some reading in BOL about table linking between SQL Server and MS Access. This is a secured database application, so I'll need to configure the registery with the Workgroup Information file and then use sp_addlinkedsrvlogin to create mappings from the local to the MS Access logins. I'm now wondering whether this is possible and/or practical for what I'm trying to do. It appears that table linking is used to "connect" SQL and Access tables in a one-to-one relationship. My SQL tables have an OrgID column and thus contain information for as many corresponding MS Access tables as there are OrgIDs. Each Access table would receive the results of the query "SELECT * FROM tblExample WHERE OrgID = ?". Can you use linking in this way and will it scale to dozens of organizations? |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-05-29 : 19:38:03
|
| Hi ackwebHaven't ever done exactly the same thing (at least not the security bit). But here's a few questions you could answer:Are you able to maintain a live link with the SQL server, or are you wanting to keep a local copy to work with, and then update back in SQL Server?Are you updating the data through Access or only viewing it? and where is your data coming from initially?Are you using Windows authentication or SQL Server authentication?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
ackweb
Yak Posting Veteran
54 Posts |
Posted - 2003-05-30 : 00:48:00
|
| Hi rrb-In answer to your questions:1. I'm only using Access for periodic viewing (and not updating) of the data. Therefore, the table link(s) will be to my local SQL Server.2. I'm currently doing an initial upload of Access data to SQL Server. However, all subsequent data is added via my web application and stored in SQL Server.3. I'm using SQL Server authentication.Thanks for sticking with me to help resolve this! |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2003-05-30 : 12:38:10
|
| As An Afterthought F.Y.I. The runtime version of Access Xp is Free and will run .adp If your users are not creating applications or database's the runtime works just fine especialy since your useing NT Authentication.Note: .adp is more than 10 times faster than linked SQL tables and you have the added option of useing stored procudures for sorts and finds. This improves performance even more and reduces your network trafic.JimUsers <> Logic |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2003-06-01 : 19:51:21
|
| Yes, a newer version of Access would make things easier, but if you're stuck with Access 97 (ooh, I know how that feels)....You said in your last post that you're not updating the data in Access except as an initial data entry - but the original problem you posted was with the "next record" you added in Access after getting it from SQL Server leading to a strange Id. Could you clarify?Otherwise, linked tables sounds fine for what you want, but as Jim says (depending on a bunch of things) it might be a little slow.I say again, that I haven't tried auto-updating the access workgroup file (I've never had success with Access "security") -but I wish you luck all the same.How are you managing the security for the DTS job? Or are you running this periodically from SQL Server (rather than client initiated)? Personally though, I still can't see any problem with running a maketable query to get your data from SQL. This will re-create the ID field in the order you use to select from your SQL, and can all be automated to run at startup or on a button press.Not sure if any of that helps, so please feel free to ask more.--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
AutoNumber Problem in MS Access