Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ODBC, DSNLess and a dBase database

Posted on 2000-05-02
9
Medium Priority
?
956 Views
Last Modified: 2013-12-25
I'm trying to open a DBF file via ODBC without using a DSN entry. Is this possible? Does anyone have any example code of how to achieve this? What should the connection string look like? I'm using DAO 3.51 and a dBase IV database. Thanks in advance...
0
Comment
Question by:lbowers
  • 5
  • 4
9 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 400 total points
ID: 2770286
You can create your DSN dynamically with DBEngine.RegisterDatabase.

Add this code to a class:
Option Explicit

Public glngError As Long        'Contient le code de l'erreur (s'il y en a une!)
Public gstrAttributes As String 'Contient la string d'attributs
Public gstrDriver As String     'Contient le nom du driver
Public gstrDSN As String        'Contient le nom du DSN

' *********************************************************
' *** Enregistre les données de l'ODBC dans le Registry ***
' *********************************************************
Public Function RegisterDB() As Boolean
Dim errLoop As Error

    On Error GoTo ErrorHandler
   
    RegisterDB = False
    DBEngine.RegisterDatabase gstrDSN, gstrDriver, True, gstrAttributes
    RegisterDB = True
   
    Exit Function
   
ErrorHandler:
    glngError = Err.Number
End Function

You can use it this way:
    Set objDSN = New clsRegisterDB
    With objDSN
        .gstrDriver = "Microsoft dBase Driver (*.dbf)"
       
        .gstrDSN = "FO-" & CStr(bytI)
        .gstrAttributes = "DefaultDir=" & "C:\test" & vbCrLf & _
"Description=Fichier du Front Office" & vbCrLf & _
                              "FIL=dBase 5.0;"
            .RegisterDB
    End With
0
 

Author Comment

by:lbowers
ID: 2772782
The above code works (thanks), but what is the ODBC connection string for dbf files? I notice that you don't actually specify a database within the DSN, just a directory. How do you tell VB the name of the database to use? I've tried loads of variations, but I keep gettings errors!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2774718
Since dBase is not a model of a table containing tables, the folder becomes like a database and the files (tables) are specified in your queries.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:lbowers
ID: 2775184
Excellent - Thanks for your help. The points are yours, but I was wondering if you had ever come across the following problem before:

I am able to READ records via the ODBC connection, but I get an error whenever I attempt to DELETE, ADD or UPDATE records. I have set all "Read Only" properties to false. Any ideas?

Thanks again.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2775771
Show me your code.
0
 

Author Comment

by:lbowers
ID: 2776553
   Set ws = dao.CreateWorkspace("Workspace", "", "", dbUseODBC)
    Set db = ws.OpenConnection("Disk5Copy", dbDriverNoPrompt, False, "ODBC;")

sSQL = "DELETE FROM house" ' For example
db.Execute sSQL

I get "ODBC--call failed" error!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2776735
You need to add you DSN name somewhere:
Set db = ws.OpenConnection("Disk5Copy", dbDriverNoPrompt, False, "ODBC;DSN=MYDSN")
0
 

Author Comment

by:lbowers
ID: 2776909
Disk5Copy is my DSN. I find if I use "DSN=xxx" in the connections string, it doesn't work. Using the code I've show above, I can read records from the DBF file, I just can't ADD, DELETE or UPDATE! Hmmm...
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2777006
Why don't you open the dBase file directly (without going through ODBC).

http://support.microsoft.com/support/kb/articles/Q240/7/48.ASP?LN=EN-US&SD=gn&FR=0
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question