Force VB to work with Access db just opened with GetObject 1

[NorthNone]

This VB code snippet has been updating an Access database with user-level security for quite some time, but bombed over the weekend. It is based on another posting to Tek-Tips some time back.
Another automated process left two other Access databases open on the same computer. The third database (referenced below) opened properly but the code following the Set accObj command tried to run on one of the other open databases instead of the one it just opened.
How can I force it to work with the database I just opened, or alternately close the other databases automatically?

Dim accObj As Access.application
------------------------------------------------------
x = Shell(application & " " & Chr(34) & dbs & Chr(34) & " /nostartup /excl /user " & user & _
" /pwd " & password & " /wrkgrp " & Chr(34) & workgroup &
Chr(34), vbMinimizedFocus)
Set accObj = GetObject(, "Access.Application")
---------------
'Code tried to do update on the wrong database instead of
'the one just opened
--------
DoCmd.SetWarnings False
DoCmd.CopyObject "", "EmplDataBackup", acTable, "EMPLDATA"
DoCmd.RunSQL "Delete *.* from EMPLDATA", 0
DoCmd.TransferText acImportFixed, "EMPLDATA Import Specification", "EmplDATA", "\\mes\dpts\infsys\monarch\Pos\EmplData.txt", False, ""
DoCmd.SetWarnings True
Debug.Print "Done: " & Now()
accObj.CloseCurrentDatabase
accObj.Quit
Set accObj = Nothing


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[jpittawa]

This solution will always have the problem you describe. Shell() returns the task number for a stub process used by Windows to start the application specified, not the application itself. In other words, it returns the task ID for Shell() process, not the targeted program.

As you discovered, GetObject() will launch an instance of the targeted program if one is not running. However, if there are already one or more instances running, it blindly grabs one of them. You have no control over which reference is returned.

I ran into this issue several years ago. You have to use the Window 32 API to launch an instance of the program. I don't remember exactly what the solution was.

I'll dig through Dan Appleman's "Visual Basic API Programming Guide for the Win32 API" to see if I can jog my memory how I solved this problem.

 

[Swi]

Why don't you just use ADO or DAO to accomplish this task? It looks like all you are doing is opening a table and deleting it's contents and then re-importing a text file.

Swi

 

[NorthNone]

Thanks, jpittawa. I'd be interested in learning how to do this. Meanwhile, thanks to Swi for the idea to use ADO...that's worth a shot too!
JSouth

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[NorthNone]

Swi, do you have ADO code for importing the text file? It is a flat file and the specs are in the database itself.

DoCmd.TransferText acImportFixed, "EMPLDATA Import Specification", "EmplDATA", "\\mes\dpts\infsys\monarch\Pos\EmplData.txt", False, ""

TIA
JSouth

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[Swi]

I would just use a User Defined Type and use a INSERT statement. Here is an example:

Option Explicit
Dim conn As ADODB.Connection
Dim InputData As String
Private Type EmployeeData
EmployeeNumber As String * 10
EmployeeName As String * 40
EmployeeAddress As String * 40
EmployeeCSZ As String * 40
CRLF As String * 2
End Type
Dim ImportRec As EmployeeData
Dim NumOfRecords As Long
Dim Counter As Long


Private Sub Command1_Click()
'========================================================
' Connects to Access database
'========================================================
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\UsersDB.mdb"
conn.Open

'========================================================
' Deletes any data in working table
'========================================================
conn.Execute "DELETE FROM Test"

'========================================================
' Opens file for processing and calculates # of records
'========================================================
Open "C:\Test.txt" For Binary Access Read As #1
NumOfRecords = LOF(1) / 132 ' Record size

Do
Counter = Counter + 1
' Displays counter in label on the main screen
' so the user can see something is happening
Label1.Caption = "Records Imported = " & CStr(Counter)
DoEvents
' Reads and stores the text data into the database table
Get #1, , ImportRec
conn.Execute "INSERT INTO Test (EmployeeNumber, EmployeeName, " & _
"EmployeeAddress, EmployeeCSZ) VALUES ('" & ImportRec.EmployeeNumber & "','" & _
ImportRec.EmployeeName & "','" & ImportRec.EmployeeAddress & "','" & _
ImportRec.EmployeeCSZ & "')"
Loop Until Counter = NumOfRecords

' Closes and destroys objects
conn.Close
Set conn = Nothing
Close #1

' Prompts the user processing has completed
MsgBox "Done!", vbInformation
End Sub

Swi

 

[NorthNone]

SWI, thanks for the code. I've been pounding my brain into understanding it and I think I'm almost there.

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[NorthNone]

SWI, I don't mean to impose on your time, but if you have a chance could you point me in the right direction for handling date fields? Is there a more elegant way to handle dates than what I devised? I captured my date field to a string, then tested that string. If not "" I converted it to a date. Then I ran into the problem of appending the empty date. If I used 0 then I got a value in the date field in my table. So I used two conn.Execute statements, one if a date existed and one if it didn't exist (skipped the field)
TIA
JSouth
'-----------------------------------
strTermDate = ImportRec.TermDate
If Trim(strTermDate) <> "" Then
datTermDate = CDate(strTermDate)
conn.Execute "INSERT INTO " & strTable & " (SSN,
LastYrWorked, " & _
"HrmsNum, Title, LastName, FirstName, Status, Dept,
Divn, TermDate) " & _
"VALUES ('" & ImportRec.SSN & "','" &
ImportRec.LastYrWorked & "','" & _
"" & ImportRec.HrmsNum & "','" & ImportRec.Title
& "','" & _
"" & ImportRec.LastName & "','" & _
"" & ImportRec.FirstName & "','" & _
"" & ImportRec.Status & "','" & _
"" & ImportRec.Dept & "','" & _
"" & ImportRec.Divn & "','" & _
--> "" & datTermDate & "')"
Else
conn.Execute "INSERT INTO " & strTable & " (SSN,
LastYrWorked, " & _
"HrmsNum, Title, LastName, FirstName, Status, Dept,
Divn) " & _
"VALUES ('" & ImportRec.SSN & "','" &
ImportRec.LastYrWorked & "','" & _
"" & ImportRec.HrmsNum & "','" & ImportRec.Title
& "','" & _
"" & ImportRec.LastName & "','" & _
"" & ImportRec.FirstName & "','" & _
"" & ImportRec.Status & "','" & _
"" & ImportRec.Dept & "','" & _
"" & ImportRec.Divn & "')"
End If


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[Swi]

You could try something like this:

conn.Execute "INSERT INTO Test (EmployeeNumber, EmployeeName, " & _
"EmployeeAddress, EmployeeCSZ, EmployeeHireDate) VALUES ('" & ImportRec.EmployeeNumber & "','" & _
ImportRec.EmployeeName & "','" & ImportRec.EmployeeAddress & "','" & _
ImportRec.EmployeeCSZ & "'," & IIf(IsDate(ImportRec.EmployeeHireDate), "'" & _
ImportRec.EmployeeHireDate & "')", " NULL)")

Swi

 

[NorthNone]

Worked like a charm once I modified it to my specs!
Many, many thanks and a star!

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.