need alittle help here (1 Viewer)

Jon123

Registered User.
Local time
Today, 03:15
Joined
Aug 29, 2003
Messages
668
I have this code
Code:
       DoCmd.CopyObject "C:\path\Database.accdb", "Checklist", acTable, "Checklist"
Is there away to have this code change or name the database based off
the valuse of a text box on the form that has the command button that is running this code?
ex. if text.box = jon then the code would creat the checklist table in database Jon.accdb
jon
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:15
Joined
Aug 30, 2003
Messages
36,118
Try

DoCmd.CopyObject "C:\path\" & Me.TextBoxName & ".accdb", "Checklist", acTable, "Checklist"
 

Jon123

Registered User.
Local time
Today, 03:15
Joined
Aug 29, 2003
Messages
668
Sorry I think I'm confused. The code is going to copy a table from 1 database to another database. I need to create the database 1st right?

What is the command for creating a database?

jon
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:15
Joined
Aug 30, 2003
Messages
36,118
I believe it does have to exist, yes. Never used it, but take a look at the DAO CreateDatabase Method.
 

Jon123

Registered User.
Local time
Today, 03:15
Joined
Aug 29, 2003
Messages
668
ok getting closer this is what I have right now this works but I want to use the textbox to name the new db.
Code:
    Dim wsp As Workspace
    Dim dbs As Database
    Dim strDBFile As String
    strDBFile = "C:\Newdb.accdb"
    Set wsp = DBEngine.Workspaces(0)
    Set dbs = wsp.CreateDatabase(strDBFile, dbLangGeneral)
   ' DoCmd.CopyObject strDBFile, , acTable, "Table1"
    dbs.close
    Set dbs = Nothing
    Set wsp = Nothing
[\code]

When changing to this is does not work

[code]
    Dim wsp As Workspace
    Dim dbs As Database
    Dim strDBFile As String
    strDBFile = "C:\" & Me.TextBox & ".accdb"
    Set wsp = DBEngine.Workspaces(0)
    Set dbs = wsp.CreateDatabase(strDBFile, dbLangGeneral)
   ' DoCmd.CopyObject strDBFile, , acTable, "Table1"
    dbs.close
    Set dbs = Nothing
    Set wsp = Nothing
[\code]


thanks again for the help
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:15
Joined
Aug 30, 2003
Messages
36,118
What is in the textbox when that runs? What is the error, or what happens? That is the actual name of the textbox?
 

Jon123

Registered User.
Local time
Today, 03:15
Joined
Aug 29, 2003
Messages
668
Sorry my mistake had an extra quote. So now I can create a database with the name based off a form. Now lets see if I can copy a table to that new database.

thank for the support

jon
 

dallr

AWF VIP
Local time
Today, 00:15
Joined
Feb 20, 2008
Messages
81
Why are you trying to copy a table from one database to another??

Why not just have a database already setup and insert the data into that external database. This would be cleaner and much more efficient.

This link gives you some information http://allenbrowne.com/ser-37.html

And this is some code I wrote some time ago to insert items into an external db.
Code:
Function Archive()

Dim OLdData As String, DBPath As String, SQL As String, InsertTable As String

OLdData = " SELECT * FROM Your_Table_With_Old_Data"           'Records to be inserted from the old DB.
DBPath = "'c:\mydb.mdb' "                   'Path where the new DB is stored.
InsertTable = "INSERT INTO YOur_Archive_Table  IN "  'The table to insert the information in

SQL = InsertTable & DBPath & OLdData
CurrentDb.Execute SQL, dbFailOnError
End Function
Of course you can modifiy my code above and use it in a query alone as SQL statements.

This is another technique you can use.

Code:
INSERT INTO [;database=C:\YourFolder\YourDB.mdb].Table2
SELECT * FROM CurrentDBTable
 

Jon123

Registered User.
Local time
Today, 03:15
Joined
Aug 29, 2003
Messages
668
because the name of the database must change all the time.
jon
 

dallr

AWF VIP
Local time
Today, 00:15
Joined
Feb 20, 2008
Messages
81
I still don't know why u would be doing this!! Can u explain why do u want this requirement as appose to what u want to do?
 

Jon123

Registered User.
Local time
Today, 03:15
Joined
Aug 29, 2003
Messages
668
Sure I will try. So I have a database that we use to track certian procedures that get performed on equipment. Currently we are using a word doc that get printed out and passed from user to user until complete. When complete the checklist gets turned in and someone enters the data into an access database not very efficient and we lose alot of data this way. Now here is where the problems come in to play. The work is performed at customer locations where our people do not have access to the internet therefore they can't access our network so any data transfer is or can be a pain. So I have the database installed on everyone laptop. They flow goes like this User starts a checklist. They can not complete the checklist in their shift so they must pass it over the the incoming shift. To do this I have an command button that user 1 will click that will export a database file that has 1 record in it and the name will be the system and chamber that this checklist is for. Now they can copy that file to a mem stick of floppy and hand it over the the next user coming in. User 2 will copy the file to a folder on their desk top and then go into the database and click a command button and this will import the checklist that is in progress to user 2 and so on it goes. By having the different names allows more than 1 checklist in progress going on and the confusion of which one is for which system gets easier to maintain. It is a pain but without internet I either leave it as is or do it in excel then import once complete. Will see how this way goes for abit.

jon
 

Users who are viewing this thread

Top Bottom