kw66722
asked on
linking problem after moving from azure access table to azure sql server
I am moving our database from azure access which is on a Microsoft server which can not be modified using the azure portal to sql on the azure portal. The end goal is to create asp.net pages in azure. During the rewrite the user need to be able to up date the old system.
I was planning to change the odbc link to the new server information. Which I could get each user to do with instructions. The problem is that this causes an ODBC error. The issue is the SQL server table names are being qualified with dbo. and the access tables was not. Is there a way to stop the qualification at the odbc level so it is easy to relink the tables. I tried programmatically trying to create the links in the form open event but I keep getting errors.
I was planning to change the odbc link to the new server information. Which I could get each user to do with instructions. The problem is that this causes an ODBC error. The issue is the SQL server table names are being qualified with dbo. and the access tables was not. Is there a way to stop the qualification at the odbc level so it is easy to relink the tables. I tried programmatically trying to create the links in the form open event but I keep getting errors.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
DAO or ADO?
It's odd that TableDef threw an error.
But ADO tables are a different monster
It's odd that TableDef threw an error.
But ADO tables are a different monster
ASKER
When I tried with DAO qualification got an error. ADODB does not exist in Access 2013. In tried it anyway and it did not work.
It is very strange for a statement like
Dim TD As TableDef to throw an error.
You are doing something fundamentally different than what I do.
You are dropping the tables and recreating them
I change the .Connect property and then issue .RefreshLink
But I am not really thinking clearly.
TD.Name is read/write
You can recreate all your tables and they'll all come in as dbo_TableName
You can run a loop
For Each td In CurrentDb.TableDefs
If td.Name Like "dbo_*" Then
td.name = right(td.name,len(td.name) -4)
End If
Next
Just shear the dbo_ off the names.
Problem solved.
Dim TD As TableDef to throw an error.
You are doing something fundamentally different than what I do.
You are dropping the tables and recreating them
I change the .Connect property and then issue .RefreshLink
But I am not really thinking clearly.
TD.Name is read/write
You can recreate all your tables and they'll all come in as dbo_TableName
You can run a loop
For Each td In CurrentDb.TableDefs
If td.Name Like "dbo_*" Then
td.name = right(td.name,len(td.name)
End If
Next
Just shear the dbo_ off the names.
Problem solved.
ASKER
Dim TD As TableDef - created a compile error.
This reason I deleted the tables so the old tables were removed otherwise in renaming a number 1 is added to the end of the table name. Stripping the dbo_ would mean manually changing the links and then running the script. I was hoping to avoid manual changes. Since the table names are different the refresh is not working.
This reason I deleted the tables so the old tables were removed otherwise in renaming a number 1 is added to the end of the table name. Stripping the dbo_ would mean manually changing the links and then running the script. I was hoping to avoid manual changes. Since the table names are different the refresh is not working.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Let me clarify.
I have an existing access 2013 application with table linked to an Azure server that has access table. As an example there is a table called tbl_names.
I took all the access table and recreated them in azure sql server. I took a copy of the access app and manually tried to relink the tables. It did not work since the name was qualified with the dbo. The tables were not found. I then removed the old access tables from the access application and then link the sql tables, renamed the tables using a procedure I created to get the names to be the same as the ones the forms are using.
I then tried to duplicate the same process programmatically and ran into problems. I also tried changing the properties of the odbc connection that was connected to the old access Azure database to point to the new sql database and got an ODBC error.
Thanks for your help to date. I hope my explanation makes the problem clearer.
I have an existing access 2013 application with table linked to an Azure server that has access table. As an example there is a table called tbl_names.
I took all the access table and recreated them in azure sql server. I took a copy of the access app and manually tried to relink the tables. It did not work since the name was qualified with the dbo. The tables were not found. I then removed the old access tables from the access application and then link the sql tables, renamed the tables using a procedure I created to get the names to be the same as the ones the forms are using.
I then tried to duplicate the same process programmatically and ran into problems. I also tried changing the properties of the odbc connection that was connected to the old access Azure database to point to the new sql database and got an ODBC error.
Thanks for your help to date. I hope my explanation makes the problem clearer.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That resolved the compile issues the Object was not in the reference library.
It may very well resolve everything else, too.
Test and let us know!
Test and let us know!
ASKER
I am in the process of testing had other issues that got in the way. Will complete testing today.
ASKER
This is the code I am using
dim td as Object -- when I tried as tableDef got compiler error
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete
End If
Next
' I tried the two below with the qualified id and wo.
' stConnect = "ODBC;Description=nycpcDat
"DRIVER=SQL Server Native Client 11.0;" & _
"SERVER=tcp:" & stServer & ";" & _
"DATABASE=" & stDatabase & ";" & _
"UID=" & stUsername & ";" & _
"PWD=" & stPassword & ";" & _
"=True;TrustServerCertific
stConnect = "ODBC; " & _
"DRIVER=SQL Server Native Client 11.0; " & _
"SERVER=" & stServer & _
"DATABASE=" & stDatabase & "; " & _
"UID=" & stUsername & "; " & _
"PWD=" & stPassword & "; " & _
"Encrypt=Yes"
Set td = CurrentDb.CreateTableDef(s
CurrentDb.TableDefs.Append