Importing access queries as SQL Server views

[dizzle]

Every time I try to import an access query to sql server it ends up in sql server as a table not as a view. is there any way i can keep this from happening?

 

[hwkranger]

Write the view? Access SQL is not 100% compliant with T-SQL

Randall Vollen
National City Bank Corp.

 

[hwkranger]

Write the view? Access SQL is not 100% compliant with T-SQL.

You can also script out your "querys" from access.

You can use the querydef.sql and put it after the following:

CREATE VIEW DBO.<YourViewName>


Randall Vollen
National City Bank Corp.

 

[r937]

run this in query analyzer--

CREATE VIEW myview AS
[i]paste the access query here[/i]

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[hwkranger]

I forgot the AS. lol. I hate when I give a suggestion and I'm rushed and miss that.

For you, I wrote the following. Whether or not your queries are complient with T-SQL is for you to figure out.

Private Function MyTestFunction()

Dim i
Dim qd As DAO.QueryDef
Dim dbcnn As ADODB.Connection

Const cnProvider As String = "MSDataShape" '"SQLOLEDB"
Const cnNetwork As String = "DBMSSOCN" 'Use TCP/IP
Const cnSecurity As String = "SSPI"
Const cnSrvName As String = "*** YOUR SERVER NAME ***"
Const cnTimeout As Integer = 600

Dim cnDBName As String
cnDBName = "*** YOUR DATABASE NAME ***"

With dbcnn
    .Provider = cnProvider
    .ConnectionString = "DATA PROVIDER=SQLOLEDB; Network Library=DBMSSOCN"
    .CommandTimeout = cnTimeout
    .Properties("Data Source") = cnSrvName
    .Properties("Initial Catalog") = cnDBName
    .Properties("Integrated Security") = cnSecurity
    '.Properties("Network Library") = cnNetwork
    .Open
End With

    For i = 0 To CurrentDb.QueryDefs.Count - 1
        Set qd = CurrentDb.QueryDefs(i)
        
        Dim strSQL As String
        strSQL = "SET NO COUNT ON "
        strSQL = strSQL & "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo]." & qd.Name & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbCrLf
        strSQL = strSQL & "drop view [dbo]." & qd.Name
        dbcnn.Execute strSQL
        dbcnn.Execute ("CREATE VIEW DBO." & qd.Name & " AS " & qd.SQL)
    Next i

End Function

The ABOVE CODE DELETES any view that you already have with the same name, so be careful. You can change this to:

        strSQL = "SET NO COUNT ON "
        strSQL = strSQL & "if NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo]." & qd.Name & "') and OBJECTPROPERTY(id, N'IsView') = 1)" & vbCrLf

        dbcnn.Execute (strSQL & " GO " & "CREATE VIEW DBO." & qd.Name & " AS " & qd.SQL)

The above code will only build views that currently do not exist. Keep in mind if you have TABLES with the same name you'll have problems And if you have any Access SQL that isn't compatable with TSQL you'll have problems...

Randall Vollen
National City Bank Corp.

 

[hwkranger]

I think in the future, I'm going to take more time to check what i write.

Corrections:

NO COUNT should be NOCOUNT

and don't use the second piece of code. It's garbage. I don't feel like rewritting it. I noticed that it was garbage when I saw the GO.





Randall Vollen
National City Bank Corp.