External Interface or API

[oldbritthumper]

Hi All,

I am not an Access developer so please forgive my ignorance!

We have a Quality Assurance (QA) database (Access 2003) that has a "Project" table.

Currently our QA person has to fill in project data (e.g., project name, project manager, start date, etc.) from within the Access database. I can automate this process by providing the project data from an external source.

Does Access have any integration capabilities which allows it to receive external data, like:

- a command line interface for doing SQL inserts (like isql.exe)
- a formal API

I see examples of ODBC use in the manuals, but they are from within Access to connect to an external DB. I want to connect and insert into Access from the external source.

Push into access versus pull into access.

TIA,
Matt

 

[boblarson]

You can do it in several different ways, depending on the source.

If it is an ODBC compliant database that is the outside source, you may be able to connect and insert from there. If you have it on SQL Server, for example, you can use a DTS package to insert data.

If it is in Word or Excel, for instance, you can use the COM Object model to code it to insert and/or insert manuall.

 

[aikea]

Yes, you can use Windows API in your VBA code.

 

[oldbritthumper]

Hi All,

I am not an Access developer so please forgive my ignorance!

We have a Quality Assurance (QA) database (Access 2003) that has a "Project" table.

Currently our QA person has to fill in project data (e.g., project name, project manager, start date, etc.) from within the Access database. I can automate this process by providing the project data from an external source.

Does Access have any integration capabilities which allows it to receive external data, like:

- a command line interface for doing SQL inserts (like isql.exe)
- a formal API

I see examples of ODBC use in the manuals, but they are from within Access to connect to an external DB. I want to connect and insert into Access from the external source.

Push into access versus pull into access.

TIA,
Matt

Matt,

Using CDatabase (MFC Library) and its Open() call, in conjuction with SQLConfigDataSource and ODBCINST.H you can use ODBC to gain access into an Access database from an external source.

Hope this helps,
Matt

 

[oldbritthumper]

Matt,

Using CDatabase (MFC Library) and its Open() call, in conjuction with SQLConfigDataSource and ODBCINST.H you can use ODBC to gain access into an Access database from an external source.

Hope this helps,
Matt

Matt,

Yes, that is just what I was looking for.

You're brilliant.

Thanks,
Matt

 

[oldbritthumper]

Matt,

Yes, that is just what I was looking for.

You're brilliant.

Thanks,
Matt

Hey Dude, one technique that is even easier is CDatabase::ExecuteSQL.

It is more appropriate to do your insert at the datbase table level than within the table at the record(set) level.

CDatabase::ExecuteSQL will only require a few lines as compared to the above techniques.

Matt

 

[oldbritthumper]

Hey Dude, one technique that is even easier is CDatabase::ExecuteSQL.

It is more appropriate to do your insert at the datbase table level than within the table at the record(set) level.

CDatabase::ExecuteSQL will only require a few lines as compared to the above techniques.

Matt

You know, you're absolutely right. Perhaps this thread will help the next person!

Matt