Lost in code and need some help (1 Viewer)

[BrokenBiker]

I was following this thread http://www.access-programmers.co.uk/forums/showthread.php?t=91128&highlight=append+query+vba and tried to implement that code, but no luck. I know there are parts of the code that I don't understand, and I need some schoolin'.

The intent is to have the DestinationDB/file directory built into the VBA qry as a variable.

As I have it now, I get a run-time error 2465: can't find the field 'l' refered to in your expression. (That's probably because that's part of the code that I'm having trouble understanding exactly what's needed there.)

This is the code as I have it loaded in my form. The de-bugger highlights the line in red. I put my understanding/questions of the code next to the portions that I'm confused on, along with the original code.
________________________

Dim qdf As QueryDef
Dim varDatabaseName As Variant

varDatabaseName = DLookup([Me.Text1], "Main Table", "[*] = '" & Me.Text1 & "'")Below is the original code--
varDatabaseName = DLookUp("[ServerPath]", "SomeTable", "[SomeField] = '" & Me.SomeCriteria & "'")
--I assume the ServerPath is variable, SomeTable is the table you want to append to, but what's [SomeField]? Do I need this code for each field in the table?
If IsNull(varServer) Then
MsgBox ("Unknown directory.")

Else
strSQL = "INSERT INTO Main Table (*) " & _
"IN '" & varDatabaseName & "' " & _
"SELECT Main_Table.* " & _
"FROM [Main Table] " & _
"WHERE [Main Table].Date <Now()"
'I tried to replace all of the field-specific lines w/ *. Will this work?Below is the original code for this portion.

strSQL = "INSERT INTO Customers (Advisor, ClientID, C1surname, " & _
" C1firstname, C2Surname, C2firstname, " & _
" stNo, StName, Locality, postcode) " & _
"IN '" & varDatabaseName & "' " & _
"SELECT advisors, ClientNo, Client1Name1, Client1Name2, Client2Name1, Client2Name2, StreetNo, StreetName, Locality, Postcode " & _
"FROM [Client action] " & _
"WHERE [Client action].ClientNo = " & [forms]![client action amended]![clientNo] & ";"


Set qdf = CurrentDb.QueryDefs("qryMainTblAppend")
qdf.SQL = strSQL
qdf.Close

Below is the original code for this portion.
End If
Set qdf = CurrentDb.QueryDefs("SomeReusableQuery")
qdf.SQL = strSQL
qdf.Close
________________________


Attached are two DBs, TestX and TestArchiveX. Open the TestX, and click on the button to run the code. It should append the Main Table in TestX to the Main Table in TestArchiveX.

 

[BrokenBiker]

Oops. I goofed on the files. These are the correct ones. I changed the code a little bit and now get this error: Syntax error (missing operator) in query expression 'C:\'. Run-time error 3075.

This is how the code line reads now.

varDatabaseName = DLookup(Text1, "Main Table", "[*] = '" & Me.Text1 & "'")

 

[SamDeMan]

I am not sure exactly about what you are trying to do, but i will attempt to direct you with some pointers.
1. i don't think that DLookup(,,Criteria) is correct. i don't think that the asterisk works (i may be wrong on this one).
2. you are using varServer - is this a variable for the directory you are pointing to?
3. you might want to get your code working without all the tricks of finding the DB.

 

[BrokenBiker]

I am not sure exactly about what you are trying to do, but i will attempt to direct you with some pointers.
1. i don't think that DLookup(,,Criteria) is correct. i don't think that the asterisk works (i may be wrong on this one).
2. you are using varServer - is this a variable for the directory you are pointing to?
3. you might want to get your code working without all the tricks of finding the DB.

I honestly don't know if the DLookup(,,Criteria) is appropriate or not. That's one of the things that I need a little help on. If asterisks don't work for this it's not that big of a deal. I can load extra lines of code for the individual fields.

And as far as I can tell, the varServer is a variable for the directory for the ArchiveDB. I know that the rest of the code (for the SQL) needs to work also, but the file-directory as a variable requirement is really the issue at hand.

Let me give a little background on the issue. I've created a pretty extensive db that several other units are using. Unfortunately, most people don't know much about Access, so I've created a 'blank' db that starts up w/ instructions and various forms to load information unique to their organizations.

One of the functions of the database is an 'archive' function. All it is, is a series of append and delete queries designed to move large amounts of old data to the Archive db (a basic copy of the original db minus a few functions designed to store historical data). When building an append query the DestinationDB is part of the criteria. The only way to change this is by getting into design mode for the query and changing each one's destination, and--as most people aren't familiar w/ Access--this can be a bit of a problem for those other locations.

So I'm looking at developing code that does two things. 1.) Serve as an append query in VBA, and 2.) ask for the file directory of the ArchiveDB (because each organization will have their servers/folders, etc. to put their archiveDB in).

There are a few things in the original code that I don't understand, but I'm trying my best to make it work for our db.

 

[SamDeMan]

why don't you set up a browse button so that the user can choose the location?

 

[BrokenBiker]

why don't you set up a browse button so that the user can choose the location?

Actually, I figured this part out. Now I'm working on the SQL portion of the code.

http://www.access-programmers.co.uk/forums/showthread.php?t=106480

Thanks for taking the time to respond.


I ended up creating a form & table for the user to input the new file path. The DLookup/variable server get the ServerPath from that....But now I'im having trouble w/ the SQL portion in VBA.

 

[SamDeMan]

i just replied in the other posting.

sam