Using variables as query names

[jamesmor]

Hello,

I've got a problem that I can't seem to figure out. Here's some back story on the system I work with. In the company I work for I have 32 separate sales divisions. I need to pull each division's customers on a weekly basis and put them into their own tables. Now the database that holds the customer information is on SQL Server, and I've been told that anytime I hit the SQL server I need to use pass-through queries (which isn't a problem).

I also use this database for other reporting jobs that I do, which come from the same SQL server and I'm trying to keep bloat to a minimum so I do as much as possible with code, and I try to not have static files laying around either (tables or queries).

Now that you've got some back story, here's my real issue.

Set sqlConn = New ADODB.Connection
    sqlConn.ConnectionString = "odbc;DRIVER=SQL Server;Server=*****;Database=*****;Uid=*****;Pwd*****;"
    sqlConn.Open strConn, "******", "*******"

Set sqlRecordSet = New ADODB.Recordset
sqlQuery = "SELECT DISTINCT vendor_ref.parent_vendor_id FROM vendor_ref;"
sqlRecordSet.Open sqlQuery, sqlConn, adOpenKeyset, adLockOptimistic


With sqlRecordSet
Do While Not .EOF
strParentVendorID = .Fields(0)
'MsgBox (strParentVendorID) this is only used to verify what sqlparentvendorid contained
Set sqlQueryDef = db.CreateQueryDef(strParentVendorID & "-Pull")
sqlQueryDef.Connect = "odbc;DRIVER=SQL Server;Server=olorin;Database=myinfo;Uid=amuadmin;Pwd=@muDB0wner;"
sqlQueryDef.SQL = "SELECT accounts.first_name, accounts.last_name, accounts.email" & _
    " FROM accounts INNER JOIN vendor_ref ON accounts.vendor_id = vendor_ref.vendor_id" & _
    " WHERE (((accounts.email) Is Not Null And (accounts.email) Not Like '% %' And (accounts.email) Not Like '%@'" & _
    " And (accounts.email) Not Like '@%' And (accounts.email) Not Like '%.GOV' And (accounts.email) Not Like '%.US'" & _
    " And (accounts.email) Not Like '%INFOUSA%') AND ((accounts.created_date)<=GetDate()-30) " & _
    " AND ((vendor_ref.parent_vendor_id)='" & strParentVendorID & "')) " & _
    " GROUP BY accounts.first_name, accounts.last_name, accounts.email;"
sqlQueryDef.Close

strQueryName = strParentVendorID & "-Pull"

strCreateTable = "Select * insert into & strParentVendorID & from & strQueryName &"

DoCmd.RunSQL ("Select * Into  '" & strParentVendorID & "'  From   '" & strQueryName & "' ")
.MoveNext
Loop
End With

basically I get the vendor codes for all the divisions in the recordset. then I roll through that recordset making the pass-through queries. and while I'm doing that I'm also trying to create tables from those pass-through queries

the line I'm having problems with is

DoCmd.RunSQL ("Select * Into '" & strParentVendorID & "' From '" & strQueryName & "' ")

It keeps throwing runtime error 3450

any help would be greatly appreciated.

 

[jamesmor]

Your method of using code is what actually causes bloat. Linked tables and stored querydefs require less processing at runtime and therefore less work space and therefore cause less bloat.

As to the 3450 error, build the string separately so it is easier to see what it looks like.

I think you are using single quotes when you should be using square brackets.

DoCmd.RunSQL ("Select * Into [" & strParentVendorID & "] From [" & strQueryName & "];")

now I'm really confused as I've read that using linked tables cause more bloat (from a different forum) and less bloat from here. I've also read that using linked tables isn't a really good idea because it causes more network traffic, as all the records from a remote database are sent to Access and then Access processes the queries locally. Is this actually true? Or should I push to be able to use linked tables and stored query defs?

 

[jamesmor]

Pat,

I thank you greatly, as that answers many questions that I had. So from what I've gotten, its no better for me to even use pass-through queries as they all queries are sent to the server for processing? Also, I don't know if you can link to any of those articles but it would be great if you could (I think I need to start from square one on optimizing.)

 

[jamesmor]

Thank you so much Pat, I've got most of what I'm trying to do done. I really appreciate your help.