access queries and sqlserver view

[lupien1]

Is there another way to import in sqlserver an "Access queries" than having to cut and paste the SQL code.

Thank you!

 

[NoCoolHandle]

No

 

[NoCoolHandle]

Actually..

YES - you could type it in

 

[nomadicalloy]

how about copy and paste

SQL Help Blog

http://www.xpherion.com/blog.asp

 

[gmmastros]

lupien1

The format for SQL Server stored procedures is different than the format for Access queries. It's true that some of the queries will require very little modification, but you can't rely on all your queries working. That's why it is better to handle each one seperately.

Of course, for each query, you should start with cut/paste, copy/paste, or (as NoCoolHandle suggests) typing it in. Undoubtedly, you will find some queries that will not compile in SQL Server. If you have problems with a query, post it here, along with the error message and a detailed description of what the query should do.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[NoCoolHandle]

Been doing some thinking. (always dangerous)

Using VB or VBA or VBScript you could do this

For the heck of it I just tossed a quick script together. You will need to edit your database name\servername in the connections open method and also edit the name and path to your Access database.

Create a file (text) and save it as
[red]something[/red].vbs
[blue] Edit Everything in [red]Red[/red][/blue]
Then doublclick it

On Error Resume Next
    
    Set fso = CreateObject("Wscript.FileSystemObject")
[green]'I was having errors building the engine object, I think [/green]
[green]'it is because I have AccessXP instaled. 

' With any luck it will work for your.. In VB it worked 
'fine, it just went wacko when I turneed it into a 
'vbscript. Try the .35 syntax if the first fails.
[/green]'
    Set dbe = CreateObject("Dao.DBEngine")
[green]      'Set dbe = CreateObject("DAO.DBEngine.35")[/green]
	if err.number <> 0 then
		msgbox "No engine"
	end if
    Set cn = CreateObject("Adodb.Connection")
    
    Set db = dbe.OpenDatabase([red][i]"E:\databases\Directions.mdb"[/i][/red])
    cn.Open "provider=sqloledb;server=[red][i]localhost[/i][/red];database=[red][i]test[/i][/red];integrated security=sspi"
    Set ts = fso.OpenTextFile("c:\viewerrors.txt", 2, True)
    
    For Each qd In db.QueryDefs
        cn.Execute "Create View dbo." & Replace(qd.Name, "~", "") & " as " & Replace(UCase(qd.SQL), "DISTINCTROW", "Distinct")
        If Err.Number <> 0 Then
            ts.WriteLine "QueryDef Create Failure: " & qd.Name & "; " & qd.SQL
            Err.Clear
        End If
    Next

    ts.Close
    Set ts = nothng
    Set fso = Nothing
    cn.Close
    Set cn = Nothing
    db.Close
    Set db = Nothing
    Set dbe = Nothing
msgbox "Done"