Solved

help with query and vba code

Posted on 2013-07-01
17
459 Views
Last Modified: 2013-07-24
Select field1, field2 into qry1-[Select User Name]
From table1
where user=[Select User Name]

I'm not sure if I can add parameter "User Name" to Qry1. As you understand Name of the table should be "qry1-user".

Please see form and button in the attached db
New-Microsoft-Access-Database.accdb
0
Comment
Question by:maximyshka
  • 6
  • 2
  • 2
  • +5
17 Comments
 
LVL 12

Expert Comment

by:duttcom
Comment Utility
It's unclear what you want to do. What are you expecting to happen when the button is clicked?
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
0
 

Author Comment

by:maximyshka
Comment Utility
This is good but the main creativity needs to create new table during run-time.

Therefore I asked how can I add a parameter to make table query:

Select field1, field2 into qry1-[Select User Name]
From table1
where user=[Select User Name]

see the part after the word info
0
 
LVL 26

Expert Comment

by:MacroShadow
Comment Utility
Sorry, I don't understand.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
>>I'm not sure if I can add parameter "User Name" to Qry1

You can only do that if you are building the SQL statement dynamically and then executing it via VBA.

The more important question, though, is: why do you want to do that?
0
 

Author Comment

by:maximyshka
Comment Utility
Sure.  I have around 10 users.  I want with the running command to create local tables with the additions of user names. All these local tables are needed to support updates in multi-user environment.  This is needed to resolve locking issues.

Of course, this is temporarily solution which will be later resolved by using SQL Server.  But for now I need this statement in VBA Code.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
With a proper Access architecture--that is, a back end file with the permanent data tables, and each user having his/her own copy of a front end file with the queries, forms, reports, and VBA code, there is NO need at all to do this.

If each user has his/her own instance of the front-end, simply create these user-specific tables in the front end.  I do this quite frequently.  There are times when it is useful to me to use one or more "temporary" tables to facilitate report calculations, and by having these tables created in the front end they are always user-specific.
0
 

Author Comment

by:maximyshka
Comment Utility
I understand, but all 10 users have constant update. I tried, but locking prevents from updating tables.

Therefore, I think to create temp tables with each user updates his own table.

So, the question is to automate make table query.  Therefore I asked how to automate "make table" query in VBA Code by inserting user name as parameter into query name. This probably have to be done in loop since I have 10 users.  Example

Select field1, field2 into qry1-[Select User Name]
From table1
where user=[Select User Name]         user name=peter

Select field1, field2 into qry1-[Select User Name]
From table1
where user=[Select User Name]         user name=mike

Select field1, field2 into qry1-[Select User Name]
From table1
where user=[Select User Name]         user name=nik

I need to automate it.  No hard coding of names please
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<I understand, but all 10 users have constant update. I tried, but locking prevents from updating tables.

Therefore, I think to create temp tables with each user updates his own table.
>>

 I think you need to go back and look at the design of your tables if you are having locking issues and consider a more transactional approach to the updates.

 By that I mean that each user adds records to a transaction table and when the data is viewed, it takes those transactions into account.

 Because each user is simply adding records, there is never a concurrency issue.

 You might even use an update process which processes all the transactions and updates the base data, then deletes the transactions or archives them.

 This is a general technique that you use when you have a high number of simultaneous updates.

<<Of course, this is temporarily solution which will be later resolved by using SQL Server. >>

  I would be interested to hear why you think SQL Server would solve this problem, as it functions basically the same way as JET does in terms of locking.

 You have asked a few questions now about this problem and I think rather then trying to break it down to smaller steps, we should step back and ask if the database design is correct for what your trying to do.

No one seems to understand what it is your actually trying to accomplish, so we are all having a tough time giving you good answers.

Jim.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Change the query to the following:
Select field1, field2 into qry1-Select_User_Name
From table1
where user=[Select User Name]

Open in new window

name the query, qUserTemplate

====================================
In your VBA code, do the following:
Dim qd As QueryDef
Dim strSQL As String

strSQL = dbEngine(0)(0).Querydefs("qUserTemplate").SQL

set qd = new QueryDef
qd.SQL = Replace(strSQL, "Select_User_Name", "peter")
qd![Select User Name] = "peter"
qd.Execute

qd.SQL = Replace(strSQL, "Select_User_Name", "mike")
qd![Select User Name] = "mike"
qd.Execute

qd.SQL = Replace(strSQL, "Select_User_Name", "nik")
qd![Select User Name] = "nik"
qd.Execute

set qd = nothing

Open in new window


=================
You can not use  parameters as named objects, such as tables and queries.  You must do your own string substitution in the SQL.

Alternatively, you could update the SQL in the actual query and then restore it to its original form when you are finished running each make table query.
0
 

Author Comment

by:maximyshka
Comment Utility
Thanks. The only issue is that it is replacing original table.  This is not the case.  I want to leave original table and create new table based on user initials (which serves as user name).

So, every time query runs it will create a new table leaving existing tables.

Please see query 4, table 3 and module 2
Database-2.accdb
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
Add this function:

Public Function TableExistence(TableName As String) As Boolean

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim SQL As String

TableExistence = False

SQL = "SELECT NAME " & _
        "FROM  MSysObjects " & _
        "WHERE TRIM(UCASE(NAME)) = '" & Trim(UCase(TableName)) & "';"

Set DB = CurrentDb()
Set RS = DB.OpenRecordset(SQL)

If RS.EOF = True Then
    TableExistence = False
Else
    RS.MoveFirst
    If Trim(UCase(RS!NAME)) = Trim(UCase(TableName)) Then
        TableExistence = True
    End If
End If

Set RS = Nothing
Set DB = noting

End Function

Open in new window


Then the code will be something along the lines of:

    If TableExistence(TblName) = True Then
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, TblName
        DoCmd.SetWarnings True
    End If

Open in new window

0
 

Author Comment

by:maximyshka
Comment Utility
Not working or I'm doing something wrong.

In additions, I'm not sure where to put lines below

 If TableExistence(TblName) = True Then
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, TblName
        DoCmd.SetWarnings True
    End If

Still removing existing tables

Please see attached db
Database-2-3.accdb
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I don't have Acc07 loaded.

You need to either make TblName a variable and set it to the user's table name or replace it with "peter" or whatever.
0
 

Author Comment

by:maximyshka
Comment Utility
You need to either make TblName a variable and set it to the user's table name

How can I do it? I mean in code?

Where to put lines


 If TableExistence(TblName) = True Then
        DoCmd.SetWarnings False
        DoCmd.DeleteObject acTable, TblName
        DoCmd.SetWarnings True
    End If

What version of Access do you have? Would you like me to save database under earlier version (mdbtype format)?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
@maximyshka

It would help to repost the database in an mdb format.

Have you tried the approach I outlined in my comments?
0
 
LVL 26

Expert Comment

by:jerryb30
Comment Utility
 
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

7 Experts available now in Live!

Get 1:1 Help Now