Solved

linking problem after moving from azure access table to azure sql server

Posted on 2014-12-03
12
411 Views
Last Modified: 2014-12-12
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.
0
Comment
Question by:kw66722
  • 6
  • 5
12 Comments
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 333 total points
ID: 40479905
I'll go sideways:
The Azure link strings had a gotcha in them that was unexpected.
I've forgotten it exactly, but here was my connection string

I'd run through the tabledefs collection and set the string:


linkstring = "ODBC;Description=TI_Azure;DRIVER=SQL Server Native Client 10.0;
SERVER=mydeadazureserver.database.windows.net;
UID=TheAzureLoginName@mydeadazureserver;
PWD=mycleartextpassword;
APP=Microsoft Office 2003;
WSID=myComputername;
DATABASE=myDatabase;"

                If TD.Connect <> linkstring Then
                    TD.Connect = linkstring
                End If

The gotcha was the UID, I think
The documentation suggested
UID=TheAzureLoginName
would do it but
UID=TheAzureLoginName@mydeadazureserver
was actually required.
0
 
LVL 1

Author Comment

by:kw66722
ID: 40479919
I get a data conversion error.

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 stLocalTableName
        End If
    Next
     
' I tried the two below with the qualified id and wo.

            ' stConnect = "ODBC;Description=nycpcDatabase;" & _
                    "DRIVER=SQL Server Native Client 11.0;" & _
                    "SERVER=tcp:" & stServer & ";" & _
                    "DATABASE=" & stDatabase & ";" & _
                    "UID=" & stUsername & ";" & _
                    "PWD=" & stPassword & ";" & _
                    "=True;TrustServerCertificate=False;Connection Timeout=30;"
                   
         stConnect = "ODBC; " & _
                     "DRIVER=SQL Server Native Client 11.0; " & _
                     "SERVER=" & stServer & _
                     "DATABASE=" & stDatabase & "; " & _
                     "UID=" & stUsername & "; " & _
                     "PWD=" & stPassword & "; " & _
                     "Encrypt=Yes"
   
 Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40480003
DAO or ADO?
It's odd that TableDef threw an error.
But ADO tables are a different monster
0
 
LVL 1

Author Comment

by:kw66722
ID: 40480163
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40481151
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.
0
 
LVL 1

Author Comment

by:kw66722
ID: 40482016
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.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 26

Accepted Solution

by:
Nick67 earned 333 total points
ID: 40482062
I am afraid you have me thoroughly confused.
I normally do NOT delete/create the tables.
I construct a new connection string, update the appropriate tabledef's .connect property and then issue .refresh.
BAM!
It's done, all the tables point to the new connection string.
It is the coding equivalent of the actions of the Linked Table Manager

The code you posted shows the tables clearly getting whacked
   For Each td In CurrentDb.TableDefs
         If td.Name = stLocalTableName Then
             CurrentDb.TableDefs.Delete stLocalTableName
         End If
     Next


and then recreated
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
     CurrentDb.TableDefs.Append td


When I originally created the tables for SQL Server the were all dbo.tblTableName
In Access, I took off the dbo. part
When I hover the mouse over the table, I see the connection string.
The TABLE part of the string has the SQL Server name dbo.tblTableName
So, my Access table objects do not have the same name as my SQL Server table objects.
It's never been a problem.
So, I don't understand what this Since the table names are different the refresh is not working. means

But your syntax here looks ok
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
From the help
Set tabledef = database.CreateTableDef (name, attributes, source, connect)

The issue is the SQL server table names are being qualified with dbo. and the access tables was not.
Why do you believe this has anything to do with it? In the syntax stLocalTableName is the new objects Access name, and stRemoteTableName is the SQL Server object's name.  Heck you can connect to dbo.tblSerious and call it 'foobar' in Access.  It'll work.

but I keep getting errors.
I get a data conversion error.

So, let's see the errors.
I figured the grief was in the .connect property.
The Azure connect string was a bear to debug
Damn thing was case-sensitive and picky about spaces too.
One of the gotchas was in what you have hidden in stUsername

It ABSOLUTELY had to be
TheAzureLoginName@mydeadazureserver
Not
TheAzureLoginName
and not
TheAzureLoginName@mydeadazureserver.database.windows.net

I was hoping to avoid manual changes.
Mine ran from autoexec.
If Application.path was c:\azure, it relinked all the tables to the azure server
If Application.path was c:\local, it relinked everything to the inhouse SQL Server
No use input required.
0
 
LVL 1

Author Comment

by:kw66722
ID: 40482097
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.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 40483914
<<It is very strange for a statement like
Dim TD As TableDef to throw an error.>>

 It's the references.   For Access 2013, there should be a reference set for:

Microsoft Office 15.0 Access database engine Object

 and if you check the Object Browser, it still shows up as "DAO" ;)

Jim.
0
 
LVL 1

Author Comment

by:kw66722
ID: 40487724
That resolved the compile issues the Object was not in the reference library.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40487728
It may very well resolve everything else, too.
Test and let us know!
0
 
LVL 1

Author Comment

by:kw66722
ID: 40496706
I am in the process of testing had other issues that got in the way. Will complete testing today.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now