Form development ideas

  • Thread starter Thread starter Ecapodieci via AccessMonster.com
  • Start date Start date
E

Ecapodieci via AccessMonster.com

I am attempting to create a form that does the following, but i am stumped on
the best way to approach this. It needs to be used by people that have
limited Access skills therefore i am trying to make it as user friendly as
possible.

I have a SQL server that is hosting archive information in numerous tables. I
have set up a view joinging some of these tables to create an 'audit' view.
This information is archived for one year and has over 30 million records. I
am wanting to create an Access databse, linked to this ODBC, with a form
where users can go and select a state or other information and then click a
button and a table is created with the audit information requested.

I would love for the table that is being created, to be exported to a new
database that takes on the name for the state that was selected in the form,
along with the words 'audit register' at the end.

EXAMPLE - GA Audit Register

This seems pretty straight forward but i think im taking the long approach.

Any help is greatly appreciated.

Emanuele
 
As you indicate a link by ODBC, I am assuming you are using an MDB and,
where needed, DAO code in the VBA.

Create a Form allowing the user to enter the selection criteria, use that to
modify an SQL string (remembering that there are a few differences between
the SQL used for Microsoft SQL Server and that used for the Jet database
engine), which you derived for SQL Server, and use it to create a Make-Table
Query.

As for creating another database, look in help for the DAO command,
CreateDatabase.

Larry Linson
Microsoft Access MVP
 
thanks Larry. Im happy to know that i wasnt crazy and that i wasnt taking the
long way. I will look into yoru suggestions right away. It will surely lead
me in a better direction that i was going.

thanks again
emanuele

Larry said:
As you indicate a link by ODBC, I am assuming you are using an MDB and,
where needed, DAO code in the VBA.

Create a Form allowing the user to enter the selection criteria, use that to
modify an SQL string (remembering that there are a few differences between
the SQL used for Microsoft SQL Server and that used for the Jet database
engine), which you derived for SQL Server, and use it to create a Make-Table
Query.

As for creating another database, look in help for the DAO command,
CreateDatabase.

Larry Linson
Microsoft Access MVP
I am attempting to create a form that does the following, but i am stumped
on
Click to expand...
[quoted text clipped - 26 lines]
Emanuele
Click to expand...
Click to expand...
 
Here is my code for generating a new database using the name of the state
that is selected on the form. I run into a few problems and cannot figure out
what is wrong...


Private Sub Command19_Click()

Dim sPath As String
Dim LFilename As String
Dim gState As String
Dim database As String


'Get default Workspace
Set workspace = DBEngine.Workspaces(0)

'Path and file name for new mdb file
LFilename = "H:\Managers\Users\Audit" & getState & " " & "Audit Register.
Mdb"

'Make sure there isn't already a file with the name of the new database
If Dir(LFilename) <> "" Then Kill LFilename

'Create a new mdb file
Set database = workspace.CreateDatabase(LFilename, dbLangGeneral)

'For lookup tables, export both table definition and data to new mdb file
DoCmd.TransferDatabase acExport, "Microsoft Access", "LFilename", acTable,
"Audit Register", "Audit Register", False

db.Close
Set db = Nothing
End Sub


The word getState is a variable that i have set up based on what is in the
form combo box for the state that is selected. The "'Create a new mdb file"
area is where i am running into problems.

emanuele

thanks Larry. Im happy to know that i wasnt crazy and that i wasnt taking the
long way. I will look into yoru suggestions right away. It will surely lead
me in a better direction that i was going.

thanks again
emanuele
As you indicate a link by ODBC, I am assuming you are using an MDB and,
where needed, DAO code in the VBA.
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...
 
yes
Here is my code for generating a new database using the name of the state
that is selected on the form. I run into a few problems and cannot figure out
what is wrong...

Private Sub Command19_Click()

Dim sPath As String
Dim LFilename As String
Dim gState As String
Dim database As String


'Get default Workspace
Set workspace = DBEngine.Workspaces(0)

'Path and file name for new mdb file
LFilename = "H:\Managers\Users\Audit" & getState & " " & "Audit Register.
Mdb"

'Make sure there isn't already a file with the name of the new database
If Dir(LFilename) <> "" Then Kill LFilename

'Create a new mdb file
Set database = workspace.CreateDatabase(LFilename, dbLangGeneral)

'For lookup tables, export both table definition and data to new mdb file
DoCmd.TransferDatabase acExport, "Microsoft Access", "LFilename", acTable,
"Audit Register", "Audit Register", False

db.Close
Set db = Nothing
End Sub

The word getState is a variable that i have set up based on what is in the
form combo box for the state that is selected. The "'Create a new mdb file"
area is where i am running into problems.

emanuele
thanks Larry. Im happy to know that i wasnt crazy and that i wasnt taking the
long way. I will look into yoru suggestions right away. It will surely lead
Click to expand...
[quoted text clipped - 8 lines]
Click to expand...
 
If the name of the state were "Arizona", what would you want the fully
qualified File name of that new MDB to be? From the code you show, it
appears it would generate:

H:\Managers\Users\AuditArizona Audit Register.MDB

That is, with a space between the path and the file name, and a space in the
file name. Spaces in names can cause some unexpected effects, so it's good
to avoid them.

It you are having a "few problems," and need help, it is always a good idea
to give as much detail as you know... like which line is giving the error,
and the exact error message, if any, or any "effects" you can observe.

BTW, you lost me on the next two posts where you just added "yes" or "OK,
maybe not" at the top.

Larry Linson
Microsoft Access MVP

Ecapodieci via AccessMonster.com said:
Here is my code for generating a new database using the name of the state
that is selected on the form. I run into a few problems and cannot figure
out
what is wrong...


Private Sub Command19_Click()

Dim sPath As String
Dim LFilename As String
Dim gState As String
Dim database As String


'Get default Workspace
Set workspace = DBEngine.Workspaces(0)

'Path and file name for new mdb file
LFilename = "H:\Managers\Users\Audit" & getState & " " & "Audit
Register.
Mdb"

'Make sure there isn't already a file with the name of the new database
If Dir(LFilename) <> "" Then Kill LFilename

'Create a new mdb file
Set database = workspace.CreateDatabase(LFilename, dbLangGeneral)

'For lookup tables, export both table definition and data to new mdb
file
DoCmd.TransferDatabase acExport, "Microsoft Access", "LFilename",
acTable,
"Audit Register", "Audit Register", False

db.Close
Set db = Nothing
End Sub


The word getState is a variable that i have set up based on what is in the
form combo box for the state that is selected. The "'Create a new mdb
file"
area is where i am running into problems.

emanuele

thanks Larry. Im happy to know that i wasnt crazy and that i wasnt taking
the
long way. I will look into yoru suggestions right away. It will surely
lead
me in a better direction that i was going.

thanks again
emanuele
As you indicate a link by ODBC, I am assuming you are using an MDB and,
where needed, DAO code in the VBA.
Click to expand...
[quoted text clipped - 16 lines]
Emanuele
Click to expand...
Click to expand...
Click to expand...
 
Back
Top