Is this even possible?

[cubbyamber]

I want to import an excel spreadsheet into my database. The import works fine but I was wondering if there is a way to assign the records to a specific client.

Here's what happens:

A client sends me 2000 orders. I import it into the tblOrderDetails. I want to assign a client number (which I choose) to each order that they sent me. I would like to do this without manually typing in the cilent number for each record.

Can this be done and if so, how?

Thanks in advance

Tracy

 

[FoFa]

I would create a work table (temp table, whatever). Have a form to select the client. Press an import button and import the data to the work table. Have the process run any cleanup queries needed, then move the data to your Order Details table with the client already attached. I just like to use a work table because it always seems to need some kind of cleanup when it comes from an external source. You could import directly to your Order Details table and pull the client from the form as well.

 

[The_Doc_Man]

I agree with FoFa. Do the import as a separate process. Then do the append, at which time you can specify a constant in the field/column corresponding to your client.

 

[cubbyamber]

Okay,

I follow you but if I use a temp table, shouldn't I do a delete query as well so that my database doesn't get bloated? I am doing this for a person who is Access Illiterate worse than I am so this is my thinking:

Putting an import button on the switchboard
Also having an append button on the switchboard where once it's clicked it asks for a Client number which is selected via a combobox. Does that sound about right?

Tracy

 

[FoFa]

Typically yes, run a delete query to clear the work table, as long as you do not have multiple people importing at the same time. Single user system, no problem.

 

[The_Doc_Man]

As to the delete query, absolutely correct.

Using the mechanism you describe is permissible. You would probably have to trigger a macro to do the steps unless you are good at writing VBA.

 

[cubbyamber]

Wow, you guys are quick.

If I use a macro to do a delete query can I make it so I need no interaction from the person using the database or do I have to create another command button?

Tracy

 

[The_Doc_Man]

A macro can be multi-step. Easily.

Build the macro. Run it by hand. When it works right, use the query wizard to build a button. "Run A Macro" is a button-wizard preset.

 

[cubbyamber]

I got the append query done. I can't do a macro to run it because the Sql is more than the allotted 256 characters. I did however, create a command button to run the append query which works fine however, I don't know how to make it so that after it appends the data it deletes it in the temp table.

Here is the code that Access automatically entered for me:

Private Sub Append_Data_Click()
On Error GoTo Err_Append_Data_Click

Dim stDocName As String

stDocName = "Append Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Append_Data_Click:
Exit Sub

Err_Append_Data_Click:
MsgBox Err.Description
Resume Exit_Append_Data_Click

End Sub

I am assuming I need to add additional code for it to delete the information from the temp table automatically. If this is true what is the code and how do I write it?

Tracy

 

[FoFa]

I would have used a Macro, but this is OK.
First this will give warnings about, you are about to run an append query, you are about to append 999 rows, etc. So if you do not want that, you need to set warnings off and back on in this script. Look in help for DOCMD.SETWARNINGS

Create a delete query and add the code to run it here. To keep it consistant, do it like this after the line "DoCmd.OpenQuery stDocName, acNormal, acEdit":
stDocName = "MyDelete Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

 

[cubbyamber]

Thanks, it worked like a charm. I didn't get any warning messages but that's okay now I just don't have to stop them.

Tracy

 

[cubbyamber]

I do have one more question:

I have attached the database for reference.

I'm not understanding how to do a pop up form so that I can add the client number to each record.

My problem is my append query doesn't give me the option to put a client number in it. I hope this makes sense.

BTW, my append command button is on my order details form. I just wanted to create it and test it out before creating a switchboard and putting it on there.

Tracy

 

[Pat Hartman]

Don't make a temporary table. That just contributes to bloat. Deleting the imported records does not free up the space. Only a compact and repair will free up space. Simply link to the spreadsheet. You can run your append query from the linked spreadsheet to the order details table.

 

[cubbyamber]

The only problem with linking a spreadsheet is that I might have 20 or more spreadsheets from different clients.

Tracy