Setting up ODBC Connection with Access database in MapBasic?

Question

Good day! i'm trying to setup an ODBC connection in my Access database through mapbasic code, but i don't how should i start this code.

I could make it manually and able to have a link between my tables to work on.

I would like to create a tools that will open and link my tables in access and update it time to time when every time i change something on my mapinfo table.

Answer 1

When working with any database from MapInfo Pro thru MapBasic you need to first establish a connection to the database. This require a data soruce which I can understand you already have.

To open the connection you have to use the Server_Connect() function:

sConnString = "DSN=GIS_DB" 
'**and potentially also a username and password
sConnString = sConnString & "UID=USERNAME;PWD=SecretCode"
nConnection = Server_Connect("ODBC", sConnString)

Now the integer variable nConnection holds the ID of the connection that you just opened to your database.

It's now time to start using this connection. MapBasic has a number of functions and statements that can be used to work with (spatial) databases:

• Server Create Map
• Server Create Style
• Server Create Table
• Server Link Table
• Register Table
• Server Refresh
• Server Set Map
• Server_Execute
• Commit Table

If you want to create a linked table, have a look at Server Link Table statement. If you want to create a live table use the Register Table statement.

Server Link Table Statement:

sCmd    = "Select * From " & sSchema & "." sTable
sTab    = PathToTableName$(sFile)

Server nConnection Link Table
    sCmd
    Toolkit "ODBC"
    Into sTab
    File sFile

Register Table Statement:

sCmd    = "Select * From " & sSchema & "." sTable
sTab    = PathToTableName$(sFile)

Register Table sTab
    Type ODBC
    Connection Handle nConnection
    Toolkit "ODBC"
    Cache OFF  '**Or ON
    Table sCmd
    Into sFile

Open Table sFile

For MS Access you probably can leave out the Schema and just use:

sCmd    = "Select * From " & sTable

Once you got your tables open, you save the changes back to the database via the Commit Table Statement.

To Refresh a table you use the Server Refresh statement. I would recommend always running a Refresh statement after committing changes.