Link to home
Start Free TrialLog in
Avatar of maximyshka
maximyshka

asked on

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
Avatar of duttcom
duttcom
Flag of Australia image

It's unclear what you want to do. What are you expecting to happen when the button is clicked?
Avatar of Joe Howard
Avatar of maximyshka
maximyshka

ASKER

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
Sorry, I don't understand.
>>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?
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.
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.
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
<<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.
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.
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
ASKER CERTIFIED SOLUTION
Avatar of Jim P.
Jim P.
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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.
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)?
@maximyshka

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

Have you tried the approach I outlined in my comments?