March 17, 2005 at 12:45 pm
HI. I have a developer who has written an application in MS ACCESS. The application was originally written against an ACCESS database and now the developer is ready to move the data to sql server. I imported the access database to sql server and now his application is having trouble with queries because of the DBO in the sql server table name. I am not familiar with ACCESS at all and the developer wants to know if there is any way in sql that i can remove the DBO from the table name. I don't know how this could be done. the DBO is the owner and is part of the table name.
Any suggestions?
Juanita
March 17, 2005 at 12:52 pm
You don't have to use dbo in the queries (although it's probably not best practice) as long as the database is owned by dbo. Is your SQL Server database owned by dbo?
A.J.
DBA with an attitude
March 17, 2005 at 12:55 pm
Yes, the owner is DBO. Are you saying that the access application would need to be modified?
juanita
March 17, 2005 at 12:59 pm
Which version of ms access is your developer using ?
As far as I remember you can change settings in the access front end so that the code can be transparent to that.
As a test make the user login part of the db_owner role in the db an let the developer try!
* Noel
March 17, 2005 at 1:13 pm
access 2000. i will add the user to the db owner role.
March 17, 2005 at 1:17 pm
How did you move the table to sql server. Use a wizard? Are you now linking tables from sql into access? Do the tables show up in access now as dbo_tablename?
March 17, 2005 at 1:25 pm
I used the DTS wizard to do the import. Yes i did link the sql tables into the access database and yes the tables do show up as dbo_tablename
juanita
March 17, 2005 at 1:34 pm
Might be over simplifying here but might as well check before you go to far. Can you just rename all the tables in access so they no longer have dbo at the start? The queries in access will be like this: select whatever from anytable but the name it looks at is the linked name which is dbo_anytable. So removing the dbo_ would be the easiest way to make the queries work.
March 17, 2005 at 1:45 pm
Juanita,
My advice was based on access ADP projects not on ACCESS linked tables!
For linked tables you just have to rename the link as suggested by Logan above (which you can't on ADP)
hth
* Noel
March 17, 2005 at 11:57 pm
Hi
I did the saame exercise some years back , maybe a bit simplistic but create an ODBC link to the SQL server as normal, File - Link Tables to link all SQL tabels to Access front end the write a small module to trim 4 CHar off front of each table name ie to remove 'dbo_' or simply rename them and then they will behave just as you previous table names did before.
The additional pitfalls were when using the import wizard , some dates were trimmed to short date fromat and hence lost accuracy , you can over come this by checking each table transformation and reselecting DateTime if not correct.
Also Access allows you to use certain SQL keywords without error which cause chaos if you don't square bracket them. eg Value , Values Stats etc
Beware
Once you are over that it should be plain sailing , I have been running a commercial system for over 2 years with Access 97 front and SQL Server 2000 back with virtually no problems once these were resolved
Any other porblems please shout , I must have already seem 'em !!
March 18, 2005 at 1:42 am
Hi,
I pretty new to SQL myself but I found a code snippet (maybe on this site) that helped alot and will solve your problem.
Sub RemoveDBO()
Dim tbl As TableDef
For Each tbl In CurrentDb.TableDefs
If Len(tbl.Connect) > 0 Then
tbl.Name = Replace(tbl.Name, "dbo_", "")
End If
Next
Set tbl = Nothing
End Sub
Use DTS to import files
then run this code and it will remove the DBO
It is important to specify a Key field in each table - problems otherwise
Once I realised the above I am/was on my way
Richard
March 18, 2005 at 9:09 am
We have many access applications with a SQL back end. We Utilize a local access table to store the connection information and visual basic for applications code to link the tables. Yes we still use DAO
The code looks like:
Private Sub cmdRelink_Click()
Dim db As Database
Dim rsRelink As Recordset
Dim tdfNew As TableDef
Dim strConnect As String
DoCmd.Hourglass True
Set db = CurrentDb
Set rsRelink = db.OpenRecordset("SELECT tblLinkedTables.localTblName, tblLinkedTables.Server, " & _
"tblLinkedTables.Database, tblLinkedTables.ServerTblName, tblLinkedTables.KeyFieldNbr " & _
"FROM tblLinkedTables;", dbOpenSnapshot)
'Drop the tables
On Error Resume Next
Do Until rsRelink.EOF
DoCmd.DeleteObject acTable, rsRelink!localTblName
rsRelink.MoveNext
Loop
CurrentDb.TableDefs.Refresh
On Error GoTo 0
rsRelink.MoveFirst
'Create the table
Do Until rsRelink.EOF
strConnect = _
"ODBC;Driver=SQL Server;" & _
"SERVER=" & rsRelink!Server & ";" & _
"DATABASE=" & rsRelink!Database & ";" & _
"Trusted_Connection=Yes"
Set tdfNew = db.CreateTableDef(rsRelink!localTblName)
tdfNew.CreateIndex
tdfNew.Connect = strConnect
tdfNew.SourceTableName = rsRelink!ServerTblName
db.TableDefs.Append tdfNew
CurrentDb.TableDefs.Refresh
rsRelink.MoveNext
Loop
Set tdfNew = Nothing
Set db = Nothing
DoCmd.Hourglass False
End Sub
The table tblLinkedtable looks like
| localTblName | Server | ServerTblName | Database |
| tblARUsrAcctNbr_SFStub | SQLServer | dbo.tblARUsrAcctNbr_SFStub | SewerFacilities |
| tblBaseMaps | SQLServer | dbo.tblBaseMaps | WaterWare |
| tblSFManholeMaint | SQLServer | dbo.tblSFManholeMaint | SewerFacilities |
| tblSFManHoleNotes | SQLServer | dbo.tblSFManHoleNotes | SewerFacilities |
| tblSFManholes | SQLServer | dbo.tblSFManholes | SewerFacilities |
| tblSFPipeNotes | SQLServer | dbo.tblSFPipeNotes | SewerFacilities |
| tblSFPipes | SQLServer | dbo.tblSFPipes | SewerFacilities |
hope that helps.
March 18, 2005 at 9:30 am
One problem with linked tables rather than a Project file is performance. We see a markable improvement with performance if we use a Project DB. One example is a query that takes 10 minutes using a linked table, but only about 10 seconds using Project. It was well worth it to us to convert all of our queries to run in project.
One warning I have is that if you want to use Project, you actually need to re-write all of your queries to use the SQL Server dialect of SQL commands. IE: single quotes rather than double quotes around strings, CASE rather than IIF, etc.
Michael Lee
March 18, 2005 at 9:38 am
Thank you everybody.. I'm going to try these different options !!!
Juanita
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply