Create MS-Access database

[silvercover]

Hi,

How can I make *.mdb or *.accdb files by coding on run-time in PB?

Thanks.

[spikey]

I'm not sure you can. The whole point of the Access runtime is that design features are disabled or not installed. You need a full install of Access to do any design work. However, if you create a complete but minimal database - ie all tables, forms, reports etc that you need to work with but without any content in the database tables (or the minimum that you need to get the thing off the ground) - then this you could distribute with the runtime.

PB supports SQLite directly which is fine for single user applications, or PostgreSQL for multi-user applications, or ODBC too...

[ts-soft]

Code: Select all

#ODBC_ADD_DSN = 1

Procedure CreateEmptyMDB(databasename.s, user.s = "", pass.s = "")
  Protected strDriver.s, strAttributes.s, L.l, result.l
  Protected *buffer.Character, len.l
  
  If FileSize(databasename) > 0
    DeleteFile(databasename)
  EndIf
 
  strDriver = "Microsoft Access Driver (*.mdb)"
  strAttributes + "CREATE_DB=" + #DQUOTE$ + databasename + #DQUOTE$ + " General"
  strAttributes + ";UID=" + user
  strAttributes + ";PWD=" + pass + ";"
  
  *buffer = @strAttributes
  len = Len(strAttributes) - 1
  For L = 0 To len
    If *buffer\c = ';'
      *buffer\c = 0
    EndIf
    *buffer + SizeOf(Character)
  Next L

  result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, strDriver, strAttributes)   ; Call the function you need from the ODBC library with the right details

  ProcedureReturn result
  
EndProcedure

[Kwai chang caine]

Waouuuuh Cool
Thanks TsSoft

[silvercover]

Thank you ts-soft. It works perfect.

[ts-soft]

you are welcome
here some more functions special for mdb

Code: Select all

#ODBC_ADD_DSN = 1

Procedure RepairMDB(databasename.s, user.s = "", pass.s = "")
  Protected name.s, strDriver.s, strAttributes.s, L.l, result.l
  Protected *buffer.Character, len.l
  
  name = GetFilePart(databasename)
  name = Left(name, Len(name) - (Len(GetExtensionPart(name)) + 1))

  strDriver = "Microsoft Access Driver (*.mdb)"
  strAttributes + "REPAIR_DB=" + #DQUOTE$ + databasename + #DQUOTE$
  strAttributes + ";UID=" + user
  strAttributes + ";PWD=" + pass + ";"

  *buffer = @strAttributes
  len = Len(strAttributes) - 1
  For L = 0 To len
    If *buffer\c = ';'
      *buffer\c = 0
    EndIf
    *buffer + SizeOf(Character)
  Next L
 
  result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, strDriver, strAttributes)   ; Call the function you need from the ODBC library with the right details

  ProcedureReturn result
  
EndProcedure

; ***************************************************************************************

Procedure CompactMDB(databasename.s, user.s = "", pass.s = "")
  
  Protected strDriver.s, strAttributes.s, L.l, result.l
  Protected *buffer.Character, len.l
  
  strDriver = "Microsoft Access Driver (*.mdb)"
  strAttributes + "COMPACT_DB=" + #DQUOTE$ + databasename + #DQUOTE$ + " " + #DQUOTE$ + databasename + #DQUOTE$+ " General"
  strAttributes + ";UID=" + user
  strAttributes + ";PWD=" + pass + ";"
  
  *buffer = @strAttributes
  len = Len(strAttributes) - 1
  For L = 0 To len
    If *buffer\c = ';'
      *buffer\c = 0
    EndIf
    *buffer + SizeOf(Character)
  Next L
  
  result = SQLConfigDataSource_(0, #ODBC_ADD_DSN, strDriver, strAttributes)   ; Call the function you need from the ODBC library with the right details
  
  ProcedureReturn result
  
EndProcedure

[silvercover]

Again thanks for providing us more functions.

since we can not make tables on run-time in PB using ODBC, Is it useless to have above functionality according to this article?
http://msdn.microsoft.com/en-us/library ... 12%29.aspx

[spikey]

Oh - I think maybe we're talking about two different things here...

There are two versions of Microsoft Access - the full version from the Office suite and a free, redistributable version that Microsoft call the "Microsoft Access Run-time" version. The Access Run-time is designed to support pre-existing Access applications/databases on desktop computers that don't have the full version of Access installed.

You can't create new databases using the "Microsoft Access Run-time" redistributable - because this would mean everyone could create Access databases without paying for an Office license... Sorry I thought that's what you meant when you said "on run-time".

You can create new Access databases with PB through ODBC during a PB program's run-time, as ts-soft's posting shows...

Sorry if I caused confusion...

[ts-soft]

You can add a dsn to the odbc driver at runtime and remove it!

Download the PBOSL Sources and have a look at ExDatabase.pbi !