Access 2000 front end for SQL 7.0 2

[DougP]

I imported data from Access 2000 database into SQL server 7.0

So the Access datbase is the same the forms etc, but the data is now linked through an ODBC connection.
The speed increase is amasing, it's real fast now.
But...
I am not able to change the data, it's read only.

Is this a problem in the ODBC or ????

TIA




DougP, MCP

 

[cheerio]

It usually means your SQL login only as Read rights. These are set table by table, view by view.

Ken

 

[DougP]

Thanks I looked at that and it did not fix it.
The problem stems from there being no unique ID for each record.
In other words several columns have the same part number more that once.
It’s part of a “one to many” relationship.
So I went into Enterprise Manger and opened the table in design view and added a Unique ID column and made it Identity seed, increment 1, this seemed to solve the problem. Now every row is totally unique. As new rows are added then it gives them the next unique number automatically.

Here is an inserting note:
In Access if you want a table not to have a Primary Key when you Link it then it automatically will not allow you to change the data. As soon as you add the table and give it at least one Primary key then you can change the data. This is why I had to add another column because when I tried to add another row it kept saying there is a Key conflict with other rows. Bizarre
DougP, MCP

 

[tlbroadbent]

Another option is to add a timestamp column to the table. That will also make it unique. Note: Timestamp in SQL Server is not a datetime column but rather a row identifier column. Only one is allowed per table like the identity column. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[DougP]

Thanks Terry
Nice WEB site
Is your DBA Microsoft?? MCDBA??
DougP, MCP

 

[SQLSister]

More proof that every table should have some kind of identifying colunm even if you don't think it needs one right now! Sooner or later it will come back to bite you.

Incidently, if you are just converting a database to Access, I would also consider changing all your Access queries to stored procedures. This will further speed up your application, especially if you have any complex ones. You can run them using a SQL pass through query in Access. If I need the data from a field in the form as a parameter for the stored procedure, I change the pass through query dynamically using VBA, then run the query to execute the stored procedure.

Judy

 

[DougP]

SQL Sister (love that name)
Is there a quick way to convert queries to sp_'s?
DougP, MCP

 

[SQLSister]

There probably is a tool somewhere to do this, but I don't know about it. What we did was take our most complex queries and those which ran on the biggest tables and converted those first. In fact, I wouldn't bother with the ones that run on little tables, you won't gain that much speed. But if you have queries with lots of joins or complex calculations, convert those. Also any queries that your app will frequently run.

You can copy the SQL that Access wrote for the query to give you a starting point on the stored procedure, but you will have to adjust for the different flavor of SQL and for the fact that you can't directly reference fields in forms. For those last, you have to make the reference an input parameter on the stored procedure and then use the variable you created as the parameter within the SQL code. Then get the information for the pass-through query dynaically from VBA to run the stored procedure. I know that sounds complicated, but it's not too bad once you get used to it. If you need some sample code for this to make sense, I can probably pull some from our app and post it.

Judy

 

[DougP]

thanks DougP, MCP

 

[tlbroadbent]

Doug,

DBA is my job title. I don't have any certifications. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Stickarm]

There are some good tools at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/access2sql.asp

to help convert Queries to SQL Server.

Hope this helps.
Phil

 

[DougP]

Oohh Cool !!!
how 'bout a star DougP, MCP