PowerBuilder DSN Creation

Question

I am new to PowerBuilder.

I want to retrieve the data from MSAccess tables and update it to corresponding SQL tables. I am not able to create a permanent DSN for MSAccess because I have to select different MSAccess files with same table information. I can create a permanent DSN for SQL server.

Please help me to create DSN dynamically when selecting the MSAccess file and push all the tables data to SQL using PowerBuilder.

Also give the full PowerBuilder code to complete the problem if its possible.

Answer 1

In Access we strongly suggest not using DSNs at all as it is one less thing for someone to have to configure and one less thing for the users to screw up. Using DSN-Less Connections You should see if PowerBuilder has a similar option.

Answer 2

• Create the DSN manually in the ODBC administrator
• Locate the entry in the registry
• Export the registry syntax into a .reg file
• Read and edit the .reg file dynamically in PB
• Write it back to the registry using PB's RegistrySet ( key, valuename, valuetype, value )

Once you've got your DSN set up, there are many options to push data from one database to the other.

You'll need two transaction objects in PB, each pointing to its own database. Then, you could use a Data Pipeline object to manage the actual data transfer.

Answer 3

You want to do the DSNLess connection referenced by Tony. I show an example of doing it at PBDJ and have a code sample over at Sybase's CodeXchange.

Answer 4

I am using this code, try it!

//// Profile access databases accdb format
SQLCA.DBMS = "OLE DB"
SQLCA.AutoCommit = False
SQLCA.DBParm = "PROVIDER='Microsoft.ACE.OLEDB.12.0',DATASOURCE='C:\databasename.accdb',DelimitIdentifier='No',CommitOnDisconnect='No'"

Connect using SQLCA;
If SQLCA.SQLCode = 0 Then
    Open ( w_rsre_frame )   
else
    MessageBox ("Cannot Connect to Database", SQLCA.SQLErrText )
End If

or

//// Profile access databases mdb format
transaction aTrx
long resu
string database 
database = "C:\databasename.mdb" 
aTrx  = create transaction 
aTrx.DBMS = "OLE DB" 
aTrx.AutoCommit = True 
aTrx.DBParm = "PROVIDER='Microsoft.Jet.OLEDB.4.0',DATASOURCE='"+database+"',PBMaxBlobSize=100000,StaticBind='No',PBNoCatalog='YES'"
connect using aTrx ;
if atrx.sqldbcode = 0 then
    messagebox("","Connection success to database")
else 
    messagebox("Error code: "+string(atrx.sqlcode),atrx.sqlerrtext+ " DB Code Error: "+string(atrx.sqldbcode))
end if
// do stuff...
destroy atrx