How do I create a DBF via ADO code? 3

[phinoppix]

Hi,

I'm trying to export records from an MDB table into a DBF. Where should I begin? I'm thinking of creating an empty DBF first, then perform a loop to copy records from MDB to DBF. Is this feasible? If not, any logical suggestions?

I hope someone could post sample codes or links on how to do it.

 

[CCLINT]

Have you already done an advance search in Tek-Tips on the subject (DBF and ADO)?

And also check out MSDN, search on: DBF and ADO *******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CajunCenturion]

You can easily perform this task in VB using ADO. Establish connections to both databases, and establish a recordset for each.

A typical connection to a foxpro database using ADO would look like the following, with the directory name being in a variable.

DBDirectory = "c:\FoxDatabase"

Set fConn = New ADODB.Connection
fConn.Open "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBF;" & _
"SourceDB=" & DBDirectory & ";" & _
"Exclusive=Yes;"

To create a DBF - ADOX does not work with Foxpro, but you can execute a CREATE TABLE SQL statement against the connection, but you need specificy the full pathname to the DBF.

fConn.Execute &quot;create table &quot; & DBDirectory & &quot;\tabname.dbf (<fld1name> <type>, <fld2name> <type>, ...)&quot;

From this point on, its routine ADO Recordset handling. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[phinoppix]

I did made keyword search throughout Tek-Tips but to no avail.But I did found CCLINT's snippet on SELECT query to a DBF. I was just about to post another thread if a CREATE TABLE query can do trick. I guess, CajunCenturion had it again JIT (just-in-time).(&quot;,)

Tnx to both of you guys. Still, I'll be posting a few more questions soon about DBF connections. Tnx.

 

[phinoppix]

Hi again!

CajunCenturion, I followed the syntax to establish a connection with path set to &quot;C:\SHAREDFOLDER&quot;. The following error occurred:

-2147467259 (80004005)
[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

 

[phinoppix]

FINALLY!

I got everything working. I reviewed CCLINTs discussion on using the SELECT statement with external databases.

CajunCenturion, I was wondering why the connect string didn't work with mine. Anyway, I reverted to using ODBC connection and made use with the EXTENDER parameter.

I did took your advice...

You can easily perform this task in VB using ADO. Establish connections to both databases, and establish a recordset for each.


Tnx to you both!!!

One question, though, could it be possible to use SQL's

INSERT INTO

statement to pass data from MDB into an existing DBF file? I am only wondering coz with CCLINTs code snippets (forgot to jot down the thread)), one variation of the SELECT statement syntax can include the database driver (in CCLINTs example, t'was PARADOX x.xx).

Tnx

 

[CajunCenturion]

Don't know why the connection failed. Perhaps if we could see the entire connection string we might be able to offer some insights.

As far as the Insert Into statement - I don't know, haven't tried it.

In any case, glad your on the right track. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CCLINT]

The INSERT INTO will work.... *******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[phinoppix]

To CajunCenturion, sorry this post came late. The snippet you gave works just fine. I posted the codes (old & new)

'This was the old procedure I used before I got the new code working
Private Sub OpenTargetOld()
    'Establishing connection to DBF
    Dim strProvider As String
    Dim strExtended As String
    
    strProvider = "Provider=MSDASQL.1;"
    strProvider = strProvider & "Persist Security Info=False;"
    strProvider = strProvider & "Extended Properties="
    'Extended properties
    strExtended = "DSN=Visual FoxPro Tables;"
    strExtended = strExtended & "UID=;"
    strExtended = strExtended & "SourceDB=c:\CWCADDON;"
    strExtended = strExtended & "SourceType=DBF;"
    strExtended = strExtended & "Exclusive=Yes;"
    strExtended = strExtended & "BackgroundFetch=Yes;"
    strExtended = strExtended & "Collate=Machine;"
    strExtended = strExtended & "Null=Yes;"
    strExtended = strExtended & "Deleted=Yes;"
    
    strProvider = strProvider & strExtended

    conTrg.Open strProvider
    Call CreateTableProc
    Set cmTrg.ActiveConnection = conTrg
    cmTrg.CommandType = adCmdTable
    cmTrg.CommandText = txtATTACHMENT
    Set rsTRG = cmTrg.Execute
End Sub

'And here's the code based on your snippet.
Private Sub OpenTarget()
    Dim strProvider As String
    strProvider = "Driver={Microsoft Visual FoxPro Driver};" & _
           "SourceType=DBF;" & _
           "SourceDB=C:\CWCADDON;" & _
           "Exclusive=Yes;"
           
    conTrg.Open strProvider
    Call CreateTableProc
    Set cmTrg.ActiveConnection = conTrg
    cmTrg.CommandType = adCmdTable
    cmTrg.CommandText = txtATTACHMENT
    Set rsTRG = cmTrg.Execute
End Sub

CCLINT, I hope you won't mind if you could post the INSERT INTO statement (or links about this) I'l need to copy records from MDB connection into DBF connection using a SQL statement (or series of statements). Something like...


INSERT INTO ???dbf??? (fldlst) IN ????DBF????
SELECT MDBfields FROM MDBTable;


Tnx to all for boosting my interest with ADO and SQL.

 

[CCLINT]

Use the IN keyword

I would have the connection set on the DBF db and inserting into the MDB table:

INSERT] INTO Tablename (field1, field2)
IN 'C:\Temp\myJetDB.MDB'
SELECT field1, field2
FROM myDBFTable


Please note the single quotes around the path.

http://msdn.microsoft.com/library/d...y/en-us/office97/html/output/F1/D2/S5A328.asp

][/b]][/i]][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[phinoppix]

Here's what I've come up but still ain't workin (my brain's about to dry up)

Dim strSQL As String
Dim myCOm As New ADODB.Command
Dim RS As New ADODB.Recordset
strSQL = "INSERT INTO '" & strFileTitle & "'.dbf "
strSQL = strSQL & "IN ['"
strSQL = strSQL & Mid(gAppPath, 1, Len(gAppPath) - 1)
strSQL = strSQL & "' 'dBASE IV;'] "
strSQL = strSQL & " (T_EMPNO, T_TDTE, "
strSQL = strSQL & "  T_TIME, T_IOCD, "
strSQL = strSQL & "  T_DATE, E_EMPNO, "
strSQL = strSQL & "  POSFTLAG) "
strSQL = strSQL & "SELECT Timedata.EmployeeID, "
strSQL = strSQL & " Timedata.Timedata, "
strSQL = strSQL & " Format(Timedata.Timedata,'hhmm') As TimeOnly, "
strSQL = strSQL & " Left(Timedata.InOut,1) As InOut, "
strSQL = strSQL & " Timedata.Timedata, "
strSQL = strSQL & " Timedata.EmployeeID, "
strSQL = strSQL & " ' ' As PostFlag "        '1 space
strSQL = strSQL & " FROM Timedata;"
    
Set myCOm.ActiveConnection = conSrc
myCOm.CommandType = adCmdText
myCOm.CommandText = strSQL
Set RS = myCOm.Execute

conSrc is just an MDB connection.
Error message: &quot;Syntax error in query. Incomplete query clause.&quot;
Actually, I really don't know much about this form of INSERT INTO statement.

Tnx.

 

[CCLINT]

Put a
Debug.Print strSQL
right before
Set myCOm.ActiveConnection = conSrc

and then review the syntax in the immediate/Debug window. You will find several errors. [/b][/i][/u]*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}