0

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.

  • Do you have a ODBC Data Source or are you trying to avoid creating a Data Source? – Peter Horsbøll Møller May 20 '15 at 7:19
  • Thanks Peter. I already created an ODBC data source manually. But i don't know if i have to code it in mapbasic to call my table in access and able to have a link between tables and have it update it. I just read some forum that i have to use the code "server_connect" to be able to connect to my access db. But i don't know how should i start coding and update my table in access using mapbasic. – Ivan May 20 '15 at 7:35
2

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.

  • Thanks Peter, this was well explained. May i know if i have 3 tables to access do i have to create 3 ODBC connection? – Ivan May 20 '15 at 8:37
  • No, you can reuse the connection for as many tables as you want - well, there is a limit somewhere but you can open a lot of tables via one connection – Peter Horsbøll Møller May 20 '15 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.