Access DSNs Automatically

[Karbon]

Code updated for 5.20+

I don't know why I posted this in beginners the first time!

---

I found this the other day in an app I wrote some months ago. I didn't write it but I can't remember who did! Still, thought I would share..

Creating a DSN for Access on the fly :

Code: Select all

Procedure database_command(command.s)
  
  cmd = AllocateMemory(Len(command))
  
  For tmp=0 To Len(command)-1
    asc=PeekB(@command+tmp)
    If asc=59
      PokeB(cmd+tmp,0)
    Else
      PokeB(cmd+tmp,asc)
    EndIf
  Next   
  
  If OpenLibrary(9,"ODBCCP32.DLL") 
    SQLConfigDataSource = GetFunction(9,"SQLConfigDataSource")
    Result = CallFunctionFast(SQLConfigDataSource,0,1, @"Microsoft Access Driver (*.mdb)", @cmd)
    CloseLibrary(9)
  EndIf 
  
  FreeMemory(cmd)
  
  ProcedureReturn Result
EndProcedure

Use it like this : (replacing the my_* stuff with the right stuff of course!)

Code: Select all

database_command("Server=RMServer;Description=my_db;DSN=my_db;DBQ=c:\my_db.mdb;UID=my_username;PWD=my_password;")

Hope it helps someone!

[Tranquil]

Looks like Rings procedures for connecting/ disconnecting databases. Also here in the tips and tricks section

[Karbon]

Oh, might be.. I had put it in one of my source files but I know I didn't write it..

Thanks to whomever did!!

[BlairH]

I have tried the above examples and can not seem to get it to work. Can someone please provide the proper sintax to call the procedure.

I have inclued my code: I get an sintax error on database calling procedure.

;DNS Database Example

#Button1 = 1
#Button2 = 2
#MyWindow = 0

Declare Database_Command(command.s)

Procedure Button1_Click()
;If Database_Command("Server=RMServer;Description="";DBQ=C:\test.mdb"+"UID="";PWD="";")=0
;MessageRequester("Error","Did Not Open",0)
;Else
;Messagerquester("OK","all ok",0)
;EndIf
EndProcedure

Procedure Button2_Click()
PlaceHolder.s
PlaceHolder="Database_Command("Server=RMServer;Description="";DBQ=C:\test.mdb"+"UID="";PWD="";")"
MessageRequester ("OK",Placeholder,0)
EndProcedure


Procedure.l Database_Command(command.s)
cmd.l = AllocateMemory(9,Len(command))
For tmp = 0 To Len(command)-1
asc = PeekB(@command+tmp)
If asc = 59
PokeB(cmd+tmp,0)
Else
PokeB(cmd+tmp,asc)
EndIf
Next

If OpenLibrary(9,"ODBCCP32.DLL")
SQLConfigDataSource = IsFunction(9,"SQLConfigDataSource")
Result = CallFunctionFast(SQLConfigDataSource,0,1,"Microsoft Access Driver (*.mdb)",cmd)
CloseLibrary(9)
EndIf

FreeMemory(9)
ProcedureReturn Result
EndProcedure

If OpenWindow(#Mywindow,100,150,450,200,#PB_Window_SystemMenu,"")
CreateGadgetList(WindowID())
ButtonGadget(#Button1,180,100,70,25,"Button 1")
ButtonGadget(#Button2,180,130,70,25,"Button 2")
Repeat
EventID = WaitWindowEvent()
Select EventID
Case #PB_EventGadget
Select EventGadgetID()
Case #Button1
Button1_Click()
Case #Button2
Button2_Click()
EndSelect
EndSelect
Until EventID = #PB_EventCloseWindow
EndIf
End
; ExecutableFormat=Windows
; EOF

thanks

Blair.

[Karbon]

Code: Select all

Procedure Button2_Click()
PlaceHolder.s
PlaceHolder="Database_Command("Server=RMServer;Description="";DBQ=C:\test.mdb"+"UID="";PWD="";")"
MessageRequester ("OK",Placeholder,0)
EndProcedure 

to

Code: Select all

Procedure Button2_Click()
PlaceHolder.s
PlaceHolder = Database_Command("Server=RMServer;Description=test;DBQ=C:\test.mdb;UID=;PWD=;")
MessageRequester ("OK",Placeholder,0)
EndProcedure 

Maybe? Too many quotes in there and you *might* have to provide a description in order for it to work.

[BlairH]

Does the procedure return a zero if the connection is not made?

I changed my database to have a user and password then changed the code to following

Procedure Button1_Click()
Result.l
Result = Database_Command("Server=RMServer;Description=Family;DBQ=C:\test.mdb"+"UID=Blair;PWD=Blair;")
If Result = 0
MessageRequester("Error",Str(Result),0)
Else
Messagerequester("OK","all ok",0)
EndIf
EndProcedure

I get the result return of zero meaning no connection? correct?

Blair.

[Karbon]

Actually I'm not sure of the return value of that function, I would assume zero would denote failure..

You still have a typo in your code

your

Code: Select all

Procedure Button1_Click()
Result.l
Result = Database_Command("Server=RMServer;Description=Family;DBQ=C:\test.mdb"+"UID=Blair;PWD=Blair;")
If Result = 0
MessageRequester("Error",Str(Result),0)
Else
Messagerequester("OK","all ok",0)
EndIf
EndProcedure

becomes

Code: Select all

Procedure Button1_Click()
Result.l
Result = Database_Command("Server=RMServer;Description=Family;DBQ=C:\test.mdb;UID=Blair;PWD=Blair;")
If Result = 0
MessageRequester("Error",Str(Result),0)
Else
Messagerequester("OK","all ok",0)
EndIf
EndProcedure

Note the semi-colon between the database file and UID... And I assume you have c:\text.db made ?