Full Version: Append Query And Function
UtterAccess Forums > Microsoft® Access > Access Forms
access2009eg
Thank you
Near All
Ocreate append query to add record to ((((((((Another Database))))))))):
Access problem:
You Should in Append Query determine the Location of Your data base ((((Another Data base))) You want to add record to it.
What I have:
I have this function:
Public Function fCurrentDBDir() As String
'code courtesy of
'Terry Kreft
Dim strDBPath As String
Dim strDBFile As String
strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
fCurrentDBDir = Left(strDBPath, InStr(strDBPath, strDBFile) - 1) &”db1”
End Function
What is the job of this function:
Give me the path of the data base that I want add record to (((((Another Data base)))))))
What I want:
I want to use this function in append query to pass the value of the function to the append query
How can I do that:
I have try many option but I can’t reach the answer:
This is My SQl
INSERT INTO tblapp( firstname ) IN 'C:\db1.mdb' SELECT Tblplayer.Nameofplayer FROM Tblplayer;
What I do: I put the function in the append query like this:
Docmd.RunSQl “INSERT INTO tblapp( firstname ) IN fCurrentDBDir SELECT Tblplayer.Nameofplayer FROM Tblplayer;”
This error appear to me:
Run-time error ‘3024”
Could not find file ‘C:\FcurrentDBDir’
I do this also
Dim mysql As String
mysql = "INSERT INTO tblapp (firstname) IN"
mysql = mysql & fCurrentDBDir
mysql = mysql & "SELECT Tblplayer. Nameofplayer "
mysql = mysql & "FROM Tblplayer;"
DoCmd.RunSQL mysql
Run-time error ‘3134’
Syntax error in INSERT INTO STATEMENT
What should I do
Is this possible
LPurvis
You just need to make sure you've properly constructed your SQL when executing it.
When you've built mysql - try just printing it to the Immediate window instead of executing it.
You'll see there if it's execution worthy.
That said - you've just missed out your quote delimiters. :-)

mysql = "INSERT INTO tblapp (firstname) IN"
mysql = mysql & "'" & fCurrentDBDir & "'"
mysql = ...

FWIW since Acc2000 isn't pretty simple to establish the path of the current application.
CurrentProject.Path returns the same information as the function.

However - you need to supply an actual filename - not just a folder path.
e.g.
mysql = "INSERT INTO tblapp (firstname) IN "
mysql = mysql & "'" & CurrentProject.Path & "\FileName.mdb '"
mysql = ...

Oassume there's a good reason you're not just using linked tables to this other database file residing on the same folder as your application. (Is there? :-)
access2009eg
Thank you for your help
I hope I have some MVP inoformation
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.