Is it even possible to open the Relationships Window and to enforce referential integrity programmatically?

Could somebody kindly point me to an article about performing a join that excludes the results from another table? I believe that it is something resembling WHERE tblFirstTable.Key...NOT IN tblSecondTable.Key or something.

modified on Tuesday, January 29, 2008 5:19:13 PM

Is it possible to utilize the TransferText function from a database table in MS Access to a TextFile object? I need to be able to write three lines of code to a file and then transfer the records from the database table to the very same file utilizing the TransferText approach because the file needs to be presented in a certain way to the .CSV file. Is this possible? I would appreciate any assistance you could provide. Here is the code so far:

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(strNewOutputString, True)
strDatePicker = dteDatePicker.Value
dteMonth = Month(strDatePicker)
a.WriteLine ("ACTUALC")
a.WriteLine (dteMonth & ",,")
a.WriteLine (dteMonth & ",,")

    'Needs to be able to transfer the text in this format using the "Standard Output" specification format.
    DoCmd.TransferText acExportDelim, "Standard Output", \_
        "tblExportCSV", strNewOutputString, False

    a.Close

modified on Friday, December 14, 2007 12:53:54 PM

If you have a column of numeric data, then have a cell with the value result of #VALUE! and then more cells in the column with the result of numbers, how do you add the numbers together without the total result having the #VALUE! result? I tried it with the AUTOSUM feature, but that function only reads the cells from the bottom up to the #VALUE! result. I tried extending the cell range for the AUTOSUM feature, but #VALUE! is still the total result. I changed the #VALUE! field to a text amount with the AUTOSUM extended to the top of the numbers in the column, and it ignores the alphanumeric field. It only does not work when one of the fields has the #VALUE! result. What is the solution? :confused::confused::confused:

Hi, guys. I have a string that represents a path to a file. However, the file is the wrong type and needs to be converted to a different file type. I need the file to be a .CSV file instead of a .XLS file type. If I had a string representing the entire path to the file, including the .XLS, how would I truncate the .XLS and replace it with .CSV? :confused::confused::confused: -- modified at 14:19 Friday 9th November, 2007

I think that I may not have stated my question clearly. What I meant was the length of the string value in the textbox not the width of the actual textbox as an object on the form. I tried to assign the value to a string and to check that but I cannot get the strTextBox.Length value either. Instead, I decided to use the FileDialog Common Dialog control. However, I am having some trouble retrieving values from that control as well. Please see the message above.

Instead of parsing through a string path representing a directory and a file name in order to only record the directory name, I decided to use the Common Dialog known as FileDialog. However, I am not certain how to retrieve the results from the File Dialog. Here is what I have so far:

    Set dlgSaveAs = Application.FileDialog(msoFileDialogFolderPicker)
    dlgSaveDirectory.Show
    txtOutputFilePath.SetFocus
    txtOutputFilePath.Text = dlgSaveDirectory.Item

For some reason, I do not get the dropdown list indicating a value for the dlgSaveDirectory. Why can't I see the drop down list? The value that I get on the last line is: "FileDialog(msoFileDialogFolderPicker)" which is assigned to the txtOutputFilePath.Text. -- modified at 13:36 Friday 2nd November, 2007

How would I get the length of a textbox using VBA when there is no such code such as:

Dim intIndex, intIndexMax As Integer
intIndexMax = txtOutputFilePath.Length  // no such thing as txtOutputFilePath.Length
For intIndex = intIndexMax To 0 Step -1
   // try to find the directory path and separate it from the file name by using InStr to find the last \\
Next

Does anybody have some code to separate the directory path from the directory path and file name combination? Please provide some assistance...

Hi, guys. I need some help with the DoCmd.OutputTo method in MS Access. I would like to export the file to a .CSV file but the only options that I have are as follows: AcFormat can be one of these AcFormat constants. acFormatASP acFormatDAP acFormatHTML acFormatIIS acFormatRTF acFormatSNP acFormatTXT acFormatXLS The code for DoCmd.OutputTo is as follows:

DoCmd.OutputTo acOutputTable, "tblInputFile", \_
    acFormatTXT, "C:\\test\\output.csv", True

I tried to use the acFormatTXT option to export to the .CSV file. I need the result to be a comma delimited list, but it comes out like this:

--------------------------------------------------------------
| USA | WFTE | SUPPORT | Global | GLOBAL | Facilities & Real | 4 | 57AU |

| USA | WFTE | SUPPORT | Global | GLOBAL | General Supply | 0 | 57AU |

| USA | WFTE | SUPPORT | Global | GLOBAL | Marketing | 0 | 57AU |

-- modified at 13:13 Monday 29th October, 2007

Greetings everybody: I need to know how to export a database query to a .CSV file instead of to a MS Excel file. The code is as follows to export to MS Excel, but how do I export to a .CSV file?

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryFinalResult", strOutputFilePath, True

Could you explain where the configuration file is in Microsoft Access? I understand in a web application, there is a configuration file, but what about in a networked version of MS Access?

Is there a way to make a database connection more dynamic, so that the database could be moved to a different directory and still function without requiring the need to change the path in the database code itself? The code I have so far is static and requires changing each time the MS Access file moves or is moved to a different directory.

Dim strDBPath As String
strDBPath = "J:\GELCO DATABASE\Headcount Database\Headcount Database.mdb"
Set dbsHeadcount = OpenDatabase(strDBPath)

Here is what I did, and interestingly enough...it works. I created code that essentially does nothing but execute a loop and pass a counter to a control. Then I added a control to the form, left it visible, but essentially hid it from the form by making the textbox's background color, border color, and fore color the exact same as the underlying form color. This provided something for the processor to do while the QueryDef was being created. I essentially needed to count to 10000. Here is the code:

'Provides time to create QueryDef qryFinalResult
Dim intCount As Integer
intCount = 0
For intCount = 1 To 10000
    txtCounter.SetFocus
    txtCounter.Text = "Count: " & intCount & "."
    intCount = intCount + 1
Next

Does anybody have a more elegant way to do this? This code prevents that error message from coming up indicating that the QueryDef that I had just created does not exist. When I step through the code myself, the processor has enough time to create the QueryDef object, but when I run it as an application, it does not have time to create it in time to use it.

I appreciate the assistance that all of you have provided so far. However, I have encountered a slight glitch with the CreateQueryDef() method. The problem is that it seems to take a while for the QueryDef to be actually created and available for use in the following code. After I create a QueryDef, I need to use it in the very next subroutine, a point in which it might not actually become available in MS Access. Is there a way to pause execution of the subroutine until the QueryDef is actually created and available for use?

I step through the code, and sometimes the code works, but then again, sometimes the code does not work. The error message I get is that "The Microsoft Jet database engine cannot find the input table or query 'qryPassThroughQuery'. Make sure it exists and that its name is spelled correctly." I cannot understand it. On one occasion it will accept the record and pass through it without a problem. On another occasion, it does not accept it at all, providing this message. Could somebody please provide some advice??? Why can't the code find the query when the line practically in front of it does just that -- creates the query that it needs in that line???

Private Sub ProcessHeadcountRecords()
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim rstQueryDef As ADODB.Recordset
Dim strPassThroughQuery As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String

strDBPath = "J:\\GELCO DATABASE\\Headcount Database\\Headcount Database.mdb"
Set dbsHeadcount = OpenDatabase(strDBPath)
Set Cnxn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & \_
    "Data Source=" & strDBPath & ""
Cnxn.Open strConn

Set rstInputFile = New ADODB.Recordset
strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS\_UNIT\], " & \_
    "\[L\_R\_G\], \[REGION\], \[JOB\_FUNCTION\], \[ACTUAL\], \[NUMINDEX\] " & \_
    "FROM TBLINPUTFILE"
rstInputFile.Open strSQLInputFile, Cnxn, adOpenKeyset, adLockOptimistic
rstInputFile.MoveFirst

Set rstHyperionMany = New ADODB.Recordset
strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS\_UNIT\], " & \_
    "\[L\_R\_G\], \[REGION\], \[JOB\_FUNCTION\], \[NUMFOREIGNKEY\] " & \_
    "FROM \[TBLHYPERIONMANY2\] ORDER BY \[NUMFOREIGNKEY\]"
rstHyperionMany.Open strSQLHyperionMany, Cnxn, adOpenKeyset, adLockOptimistic


strFileName = "qryPassThroughQuery"
txtMessageBoxText.SetFocus
txtMessageBoxText.Text = "Processing all the records from the upload file " & \_
    "to an updated file to be imported into Hyperion."
    
Do Until rstInputFile.EOF
    With dbsHeadc

I would like to be able to assign the value from a created QueryDef object to a modifiable recordset object variable. How do I go about doing this? The Microsoft documentation does not provide any explanations. Here is the code so far:

Private Sub ProcessHeadcountRecords()
Dim dbsHeadcount As Database
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim qdfNew As QueryDef
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String

strDBPath = "J:\\GELCO DATABASE\\Headcount Database\\Headcount Database.mdb"
Set dbsHeadcount = OpenDatabase(strDBPath)
Set Cnxn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & \_
    "Data Source=" & strDBPath & ""
Cnxn.Open strConn

Set rstInputFile = New ADODB.Recordset
strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS\_UNIT\], " & \_
    "\[L\_R\_G\], \[REGION\], \[JOB\_FUNCTION\], \[ACTUAL\], \[NUMINDEX\] " & \_
    "FROM TBLINPUTFILE"
rstInputFile.Open strSQLInputFile, Cnxn, adOpenKeyset, adLockOptimistic
rstInputFile.MoveFirst

Set rstHyperionMany = New ADODB.Recordset
strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS\_UNIT\], " & \_
    "\[L\_R\_G\], \[REGION\], \[JOB\_FUNCTION\], \[NUMFOREIGNKEY\] " & \_
    "FROM \[TBLHYPERIONMANY2\] ORDER BY \[NUMFOREIGNKEY\]"
rstHyperionMany.Open strSQLHyperionMany, Cnxn, adOpenKeyset, adLockOptimistic

strFileName = "qryPassThroughQuery"
Do Until rstInputFile.EOF
    With dbsHeadcount
        strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], " & \_
            "\[BUSINESS\_UNIT\], \[L\_R\_G\], \[REGION\], \[JOB\_FUNCTION\], \[NUMFOREIGNKEY\] " & \_
            "FROM \[TBLHYPERIONMANY2\] " & \_
            "WHERE \[COUNTRY\]='" & UCase(rstInputFile!\[COUNTRY\]) & "' " & \_
            "AND \[TYPE\]='" & UCase(rstInputFile!\[Type\]) & "' " & \_
            "AND \[BUSINESS\_UNIT\]='" & UCase(rstInputFile!\[BUSINESS\_UNIT\]) & "' " & \_
            "AND \[L\_R\_G\]='" & UCase(rstInputFile!\[L\_R\_G\]) & "' " & \_
            "AND \[REGION\]='" & UCase(rstInputFile!\[REGION\]) & "' " & \_
            "AND \[JOB\_F

I have Error Message #3251 that occurs when I try to update one of the fields from one table based upon the value stored in another table. I am trying to compare the values of several fields in each of two tables, and when there is a match, I would like to assign the value of the second table to the value of the first table. When all records have had assignments, then I would perform a link between the first table and another table in the database, and then export the results with a portion of the data from each of the two tables in a query. Seems easier than trying to match a composite key from one table against a composite key from the other table. Besides, I don't think it is possible to compare composite keys. It seems better to merely compare indexes. Why doesn't it permit me to update the first table based upon results from the second table? I believe it has something to do with the Lock Type because the error message states: "Error and Error #3251: ADODB.Recordset: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype." I need some assistance here. Here is the code so far:

Private Sub ProcessHeadcountRecords()
Dim dbsHeadcount As Database
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String
Set dbsHeadcount = CurrentDb

Set cmdSQLInputFile = New ADODB.Command
Set cmdSQLInputFile.ActiveConnection = Application.CurrentProject.Connection
strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_
    "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[09/12/2007 Reported\], " & \_
    "\[NUMINDEX\] FROM \[TBLINPUTFILE\]"
cmdSQLInputFile.CommandType = adCmdText
cmdSQLInputFile.CommandText = strSQLInputFile
Set rstInputFile = cmdSQLInputFile.Execute()
rstInputFile.MoveFirst

Set cmdSQLHyperionMany = New ADODB.Command
Set cmdSQLHyperionMany.ActiveConnection = Application.CurrentProject.Connection
strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_
    "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[NU

Thanks, Dave!!!

I would appreciate some info regarding how to connect to the currently open database using the CurrentDb connection string. I am not certain of the syntax. Here is what I have so far:

Dim dbsHeadcount As Database
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String

Set dbsHeadcount = CurrentDb
Set cmdSQLInputFile = New ADODB.Command   
Set cmdSQLInputFile.ActiveConnection = CurrentDb.Connection   '  IS THIS RIGHT??? IF NOT WHAT IS THE SYNTAX??? :confused::confused::confused:
strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_
    "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[09/12/2007 Reported\] " & \_
    "\[NUMINDEX\] FROM \[TBLINPUTFILE\]"
cmdSQLInputFile.CommandType = adCmdText
cmdSQLInputFile.CommandText = strSQLInputFile
Set rstInputFile = cmdSQLInputFile.Execute()
rstInputFile.MoveFirst

Set cmdSQLHyperionMany = New ADODB.Command
Set cmdSQLHyperionMany.ActiveConnection = CurrentDb.Connection
strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_
    "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[09/12/2007 Reported\] " & \_
    "FROM \[tblInputFile\]"
cmdSQLHyperionMany.CommandType = adCmdText
cmdSQLHyperionMany.CommandText = strSQLHyperionMany
Set rstHyperionMany = cmdSQLHyperionMany.Execute()
rstHyperionMany.MoveFirst

The problem is how do I connect to the CurrentDb.Connection that is currently active?

Could somebody provide an explanation for how to programmatically add a new column to an existing Microsoft Access table, or point me to a resource article?