• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

help with query and vba code

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
maximyshka
Asked:
maximyshka
  • 6
  • 2
  • 2
  • +5
1 Solution
 
duttcomCommented:
It's unclear what you want to do. What are you expecting to happen when the button is clicked?
0
 
MacroShadowCommented:
0
 
maximyshkaAuthor Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MacroShadowCommented:
Sorry, I don't understand.
0
 
Patrick MatthewsCommented:
>>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
 
maximyshkaAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
maximyshkaAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
aikimarkCommented:
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
 
maximyshkaAuthor Commented:
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
 
Jim P.Commented:
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
 
maximyshkaAuthor Commented:
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
 
Jim P.Commented:
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
 
maximyshkaAuthor Commented:
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
 
aikimarkCommented:
@maximyshka

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

Have you tried the approach I outlined in my comments?
0
 
jerryb30Commented:
 
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now