SQL Server Views

[xljunkie]

I have a SQL statement that works in SQL Server and it has FROM [xxx].[BI].[Assets] in the statement. This is a normalized view which pulls together several denormalized tables named dbo.xxx into a single view that makes it easier for a business person such as myself to understand.

I tried this code which didn't work at qdf.SQL = strSQL because there is an error in my FROM clause:

Private Sub Command0_Click()

Dim ServerConnect As New ADODB.Connection

With ServerConnect
    .ConnectionString = "Provider=SQLOLEDB; Data Source=blahblah; Integrated Security=SSPI;"
    .Open
End With


Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset


Set db = CurrentDb

Set qdf = db.CreateQueryDef(strSQL)

    strSQL = "INSERT INTO [tblTEST] (CustomerName, x, y, z) "
    strSQL = strSQL & "SELECT CustomerName, x, y, z FROM [B][xxx].[BI].[Assets][/B] WHERE blah blah"

qdf.SQL = strSQL
qdf.ReturnsRecords = True

DoCmd.SetWarnings False
DoCmd.RunSQL ("DELETE * FROM tblTEST")
db.Execute strSQL

End Sub

I assume because [xxx].[BI].[Assets] isn't in the normal format of dbo.Table that it is giving me an error.


So I tried another method of created a linked table with an ODBC connection. If I do it this way, I can pull in all the individual dbo.Tables and recreate the normalized view that [xxx].[BI].[Assets] has. From there I'll have to pull data with other queries.

Should I try to go with method #1 with VBA or method #2 with the ODBC connection in an Access linked table?

Also, I simplified the SQL statement above. I will need to use things that I know that Access doesn't normally recognize in SQL (terms such as CONCAT, using aliases, etc don't work. I don't remember the whole story but it has something to do with T-SQL and Access having its own special version of SQL correct?).

Anyway, help would be appreciated. I'm new to creating a SQL Server connection with VBA/Access and am looking to reduce wasted time. Thanks.

 

[ranman256]

Instead of doing all this in code, cant you just make the query using linked table?
ZERO CODE.

 

[james_lankford]

qdf.ReturnsRecords = True

your query does not return records


"INSERT INTO [tblTEST]"

you are inserting into a table in sql server, not in access
is this what you want ?

your table name is [tblTEST]

there is no schema or owner associated with it
is that what you want ?

and do you have permissions to create a table in sql server in that database in that schema ?