General discussion

Locked

Dynamic file names in MS Access 97

By myblais ·
I would like to have a macro in my Access 97 database to do the following:

Export tables to [CurrentUser].mbd
Where [CurrentUser] is the system variable.

Thus when user 256 executes this macro, each of the tables in the open database is exported to 256.mbd

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Dynamic file names in MS Access 97

by jbelina In reply to Dynamic file names in MS ...

This should get you started. You'll need to run the TransferDatabase for each table you want in your new mdb file.

Option Compare Database
Option Explicit

Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function USERNAME() As String
Dim strTemp As String, strUserName As String

'Create a buffer
strUserName = String(100, Chr$(0))
'Get the username
GetUserName strUserName, 100
'strip the rest of the buffer
strUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)

USERNAME = strUserName
End Function

Public Sub CreateExport()
On Error GoTo CreateExport_err
Dim myFile As Variant, strFileName As String
'This is where you want the file to go.
strFileName = "C:\TEST\" & USERNAME() & "3.mdb"
'Checks to see if the file exists since TransferDatabase requires an existing .mdb
myFile = Dir(strFileName)
If Len(myFile) <= 0 Then
'File doesn't exist, create using DAO
Dim wspDefault As Workspace, dbs As Database

Set wspDefault = DBEngine.Workspaces(0)
Set dbs = wspDefault.CreateDatabase(strFileName, dbLangGeneral)
dbs.Close
wspDefault.Close
End If
' test was the name of my input table, test2 was the name of my output table
DoCmd.TransferDatabase acExport, "Microsoft Access", strFileName, acTable, "test", "test2"

CreateExport_exit:
Exit Sub
CreateExport_err:

MsgBox "Error: " & Err.Description
GoTo CreateExport_exit

End Sub


I hope this helps... it was a quick and dirty sample just to test the concept...

Take care,
Jeff

Collapse -

Dynamic file names in MS Access 97

by myblais In reply to Dynamic file names in MS ...

The question was auto-closed by TechRepublic

Collapse -

Dynamic file names in MS Access 97

by donq In reply to Dynamic file names in MS ...

CurrentUser() is a reserved word (and procedure) to Microsft Access returning the User ID of the person currently logged into the database - be careful.

Why don't you create a "Make Table" query and use the desired source Table as the query source sending it to ? (over the internet, over a LAN, or to disk)? Have your macro call the query and send it to any PC with Access.

Collapse -

Dynamic file names in MS Access 97

by myblais In reply to Dynamic file names in MS ...

The question was auto-closed by TechRepublic

Collapse -

Dynamic file names in MS Access 97

by myblais In reply to Dynamic file names in MS ...

This question was auto closed due to inactivity

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums