SELECT INTO variable table name

[Ian P]

Hi.

I would like to be able to create on-the-fly tables using the users ID (from Windows) plus a constant. However I cannot work out

1. how to get the user name
2. how to use this within SQL.

Can anyone help please?

The query looks like
SELECT * INTO (user_name & constant)
FROM [Employee Numbers Part 1] LEFT JOIN [SOP Status Table] ON ([Employee Numbers Part 1].[SOP Number] = [SOP Status Table].[SOP Number]) AND ([Employee Numbers Part 1].[Employee Number] = [SOP Status Table].[Employee Number])
ORDER BY [Employee Numbers Part 1].[Employee Number], [Employee Numbers Part 1].[SOP Number];

I use Access 2003 and Windows XP

[TheShabz]

where is the variable being stored? Is it a form object? If so, you will use vba to create your sql string and then run it. something like

dim strSQL as String

strSQL = "SELECT * INTO " & Me.objectName & " constant FROM ...... "

docmd.RunSQL (strSQL)

[Ian P]

Thanks TheShabz
Along with the code below From MS it solved a lot of problems.
Cheers
IanP


"' Declare for call to mpr.dll.
Declare Function WNetGetUser Lib "mpr.dll" _
Alias "WNetGetUserA" (ByVal lpName As String, _
ByVal lpUserName As String, lpnLength As Long) As Long

Const NoError = 0 'The Function call was successful

Sub GetUserName()

' Buffer size for the return string.
Const lpnLength As Integer = 255

' Get return buffer space.
Dim status As Integer

' For getting user information.
Dim lpName, lpUserName As String

' Assign the buffer size constant to lpUserName.
lpUserName = Space$(lpnLength + 1)

' Get the log-on name of the person using product.
status = WNetGetUser(lpName, lpUserName, lpnLength)

' See whether error occurred.
If status = NoError Then
' This line removes the null character. Strings in C are null-
' terminated. Strings in Visual Basic are not null-terminated.
' The null character must be removed from the C strings to be used
' cleanly in Visual Basic.
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
Else

' An error occurred.
MsgBox "Unable to get the name."
End
End If

' Display the name of the person logged on to the machine.
MsgBox "The person logged on this machine is: " & lpUserName

End Sub