Solved

help with query and vba code

Posted on 2013-07-01
17
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +5
17 Comments
 
LVL 12

Expert Comment

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

Expert Comment

by:MacroShadow
ID: 39292363
0
 

Author Comment

by:maximyshka
ID: 39293701
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!

 
LVL 27

Expert Comment

by:MacroShadow
ID: 39293762
Sorry, I don't understand.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39294233
>>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
ID: 39294992
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
ID: 39295314
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
ID: 39295342
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
 
LVL 57
ID: 39296930
<<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
ID: 39297089
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
ID: 39299058
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
ID: 39299647
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
ID: 39299981
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.
ID: 39300051
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
ID: 39300218
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
ID: 39349425
@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
ID: 39349489
 
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

737 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