MS Access and SQL Server Developers Forum

• phdot_com [TS] on 20 May 10 @ 10:05 AM #

  This is for those who are using MS Access as front end, and SQL Server as Backend for data.
  
  AFAIK:
  MS Access Versions could be MS Access 2003, 2007 and later 2010.
  MS Access 2003 Runtime for distribution is not free, while Access 2007 Runtime for distribution is free.
  MS Access 2003 Applications can run in MS Access 2007 even in .mdb or mde format
  
  MS SQL Version is 2008 whether Express or Standard Version.
  SQL Express is free, and could run on Windows 7 and can server networked users.
  
  IMO:
  MS Access is a easy and feature rich rapid development tool when you know how to use it.

• phdot_com [TS] on 31 May 10 @ 07:39 PM #

  FYI:
  Microsoft release SQL Server 2008 R2 Express Edition among other versions.
  This version of express increased the db size to 10GB, previously at 4GB only.
  CPU and RAM remains at 1.

• phdot_com [TS] on 16 Sep 10 @ 05:59 PM #

  any ms access front end and mssql data back end developers there?..

• zaqyu on 16 Sep 10 @ 07:13 PM #

  Hi,
  
  I use MS Access as FE and BE but I use MS SQL too if needed. Well I agree with you, it\'s a powerful tool if you know how to unleash its power. Any tips you share will be appreciated.

• arthurjr07 on 17 Sep 10 @ 09:59 AM #

  any ms access front end and mssql data back end developers there?..

  
  
  meron pa palang gumagawa nito. hehehe
  
  hindi na kasi ganun advisable na gamitin FE ang access e.
  Unang una wala multi-threading. pag meron ka iloload na data na will take time to load, wala na, hindi na responsive yung UI mo.
  iilan ilan lang ang built in control ng access, tapos hindi ka pa pwede i-customize yung loading nung control na yun. Wala GDI+ sa access.
  Link table/ODBC lang gamit para maka-connect sa sql server. Mabagal ito. Mas mabilis pa ang oledb.
  etc etc

• phdot_com [TS] on 17 Sep 10 @ 11:26 AM #

  in some situations, you may not need multi-threading.
  diskarte na lang sa loading of data ..i use views, sometimes load data and disconnect from SQL server immediately.
  
  i also tried pulling data over VPN from MS SQL Server..
  
  So far, ok naman. It depends.

• arthurjr07 on 17 Sep 10 @ 11:45 AM #

  how about security, roles, access rights.
  
  If I want user to have read only access to the system, is there a way to I stop him to go in design mode and edit the records in the link table?
  
  how bout visual inheritance, master pages so i will have consistent looks in all forms.
  
  how bout reports?is access report as powerful as crystal or reporting services?
  
  etc

• phdot_com [TS] on 17 Sep 10 @ 12:46 PM #

  If I want user to have read only access to the system, is there a way to I stop him to go in design mode and edit the records in the link table?

  
  
  somehow yes.. there is a way to hide your codes using mde or accde. and there are codes to disable the shift key to bypass.

• phdot_com [TS] on 17 Sep 10 @ 12:49 PM #

  ow bout reports?is access report as powerful as crystal or reporting services?

  
  
  not as powerful of course as crystal. but you can do a lot of reports.

• raido_08 on 26 Sep 10 @ 02:39 PM #

  ^ for this thread.

• phdot_com [TS] on 27 Sep 10 @ 10:31 AM #

  @quietcolor
  transaction log ba yan ng MS SQL database?

• phdot_com [TS] on 09 Oct 10 @ 05:08 PM #

  @quietcolor
  yes pwede kang mag restore gamit ang full back up mo yesterday, plus the transaction log. try lang.
  
  search ka ng procedures..

• haroldsanchez on 01 Nov 10 @ 05:03 PM #

  paano ba procedure neto mga bossing? mag la lanbased din kasi ako, access ung database ko tapos sql server ung gagagamitin? para makapag update ako sa kabilang computer?

• haroldsanchez on 01 Nov 10 @ 05:04 PM #

  para nga pala to sa thesis ko, tapos ko na kasi ung system, ila lanbased na lang

• arthurjr07 on 02 Nov 10 @ 11:00 AM #

  paano ba procedure neto mga bossing? mag la lanbased din kasi ako, access ung database ko tapos sql server ung gagagamitin? para makapag update ako sa kabilang computer?

  
  
  
  kunyari ganito setup mo.
  
  Computer 1 - Database Server
  Computer 2 - client
  Computer 3 - client
  
  
  sa computer 1, gawa ka ng network share. Gawa ka ng folder sa drive C, tapos share mo yung folder na yun.
  
  sa Computer 2, baguhin mo yung connectionstring mo, yung path sa database mo gawin mong \\\\Computer1\\<Shared Folder>\\Access.mdb
  
  sa Computer 3, baguhin mo yung connectionstring mo, yung path sa database mo gawin mong \\\\Computer1\\<Shared Folder>\\Access.mdb

• phdot_com [TS] on 02 Nov 10 @ 11:54 AM #

  @haroldsanchez
  

  access ung database ko tapos sql server ung gagagamitin

  
  
  do you mean ms access ang front-end mo tapos sql server ang database backend mo?
  yung data mo ay nasa sql server?

• haroldsanchez on 02 Nov 10 @ 01:50 PM #

  Ang data ko sir ay nasa msacess, bale magaupdate cya through sqlserver

• haroldsanchez on 02 Nov 10 @ 01:53 PM #

  @sir arthur, ah sir bale khit d na ko gumamit ng sqlserver? Mag ne network sharing nalang ako tas may idadagdag lang ako sa connection string?

• arthurjr07 on 02 Nov 10 @ 02:03 PM #

  oo
  
  
  wala ka idadagdag sa connection string, babaguhin mo lang yung path nung Access file mo. ganito
  
  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\\\IP ng database server\\name nung shared folder\\myAccess2007file.accdb;Jet OLEDB:Database Password=MyDbPassword;

• phdot_com [TS] on 02 Nov 10 @ 05:37 PM #

  @haroldsanchez
  suggest ko lang. kung alam mo gumamit ng ms msql server, pwede mo rin syang gawing back end para sa access front end mo.
  mas ok sya na setup kesa ms access lang lahat.lalo na pag marami users sa LAN.
  
  may upgrade path pa kung ganyan. pwede sya gawing vb.net or visual c# or asp.net or php ang front end later on pag gusto mo.
  my opinion lang. :)

• haroldsanchez on 02 Nov 10 @ 06:44 PM #

  cge sir subukan ko sa vmware
  
  @phdot
  
  d nga ko marunong mag sql server sir eh tsaka un lang naman hinihingi samin, ung lanbased connection

• nevz on 03 Sep 12 @ 03:27 PM #

  hi guys, how do you create a lookup in ms access containing 10 years before now and 10 years from now? bale ang magiging laman ng lookup ay:
  
  2002
  2003
  2004
  .
  .
  .2022
  
  tapos next year ganito na yung magiging laman nya:
  
  2003
  2004
  2005
  .
  .
  .2023

• nevz on 11 Sep 12 @ 09:05 AM #

  hi guys, meron akong frontend at backend db. sa backend nandun yung mga table relationships. sa frontend puro linked tables lang.
  
  sa backend kapag nag-add ng record, lumalabas yung mga subdatasheets ng parent table (may maliit na + sign). pero sa frontend kapag add ako ng record dun sa parent table, hindi lumalabas yung maliit na +.
  
  may nabasa ako sa internet na wag na daw pansinin yung maliit na plus kasi hindi naman directly sa table ang gagamitin ng users bagkus gumamit na lang daw ng forms with subforms.
  
  ang tanong ko lang, gumagana kaya yung relationships ko na dinefine ko sa backend or kailangan ko bang irelate yung tables sa forms? kapag inopen ko yung relationships view sa frontend, applied naman yung nasa backend. yung nga lang kapag inopen ko directly yung linked table sa frontend, wala yung maliit na +.

• arthurjr07 on 11 Sep 12 @ 12:15 PM #

  @nevz
  
  Hindi yung + sign ang basehan ng relationship. Bukas ka ng query editor tapos drag mo yung parent table at child table. Pag me lumabas na relationship, ibig sabihin nun e nakasetup yung relation mo.

• Warren_143 on 24 Apr 13 @ 09:25 PM #

  hellow po

• mikage_ryuji on 25 Feb 16 @ 01:03 AM #

  Hi masters, need your help.
  
  I have two tables with the following data. I am doing this in MS Access and I need to execute this without relying on a subquery as it is too slow to run on so many data.
  
  Table1 - list of unique tickets
  -TicketNumber -> primary key
  -TicketCreateTimestamp
  
  Table2 - list of ticket changes by field
  -ChangeId -> primary key, autonumber
  -TicketNumber -> ticket number where a change is made
  -ChangeTimestamp -> date of the change for a given entry
  -ChangedField -> name of the field that has been changed in a form
  -NewValue -> the new value entered to the changed field after saving
  
  Task: I need to get the latest Status change within the last 24 hours for each TicketNumber. I currently have the following as my query (this works, but it runs very very slowly):
  
  SELECT
  t1.TicketNumber,
  t1.TicketCreateTimestamp,
  (SELECT TOP 1
  t2.NewValue
  FROM
  Table2 AS t2
  WHERE
  t2.TicketNumber = t1.TicketNumber AND
  t2.ChangedField = "Status" AND
  t2.ChangeTimestamp < DateAdd("d", 1, t1.TicketCreateTimestamp)
  ORDER BY
  t2.ChangeDate DESC
  ) AS LastStatusChangedToWithin24HrsFromTicketCreateDate
  FROM
  Table1 AS t1
  GROUP BY
  t1.TicketNumber,
  t1.TicketCreateDate;
  
  
  I'm thinking of using Greatest-N-Per-Group samples but for some reason I can't pull it off with this kind of setup. Please help in optimizing this query. Thanks.
• Post deleted #11617864
• Post deleted #11617868

• reiko08 on 17 Nov 16 @ 05:17 PM #

  ok ba gamitin ang accessimagine for photo uploading?